Different ways to create JDBC DataSource
- Using Tomcat JNDI Datasource
- Using Spring JNDI Datasource
- Using Spring Transaction Manager and Datasource
1) Tomcat
Example 1
Apache Tomcat provide three ways through which we can configure DataSource in JNDI context.
- Application context.xml – This is the easiest way to configure DataSource, all we need is a context.xml file in META-INF directory. We need to define Resource element in the context file and container will take care of loading and configuring it. The approach is simple but it has some drawbacks;
- Since the context file is bundled with the WAR file, we need to build and deploy new WAR even for every small configuration change. Same issue comes if your application works in distributed environment or your application needs to be deployed in different testing environments such as QA, IT, PROD etc.
- The datasource is created by container for the application usage only, so it can’t be used globally. We can’t share the datasource across multiple applications.
- If there is a global datasource (server.xml) defined with same name, the application datasource is ignored.
- Server context.xml – If there are multiple applications in the server and you want to share DataSource across them, we can define that in the server context.xml file. This file is located in
apache-tomcat/confdirectory. The scope of server context.xml file is application, so if you define a DataSource connection pool of 100 connections and there are 20 applications then the datasource will be created for each of the application. This will result in 2000 connections that will obviously consume all the database server resources and hurt application performance. - server.xml and context.xml – We can define DataSource at global level by defining them in the server.xml
GlobalNamingResourceselement. If we use this approach, then we need to define aResourceLinkfrom context.xml file of server or application specific. This is the preferred way when you are looking to share a common resource pool across multiple applications running on the server. Regarding resource link, whether to define it at server level context xml file or application level depends on your requirement.
If you are getting bored with theory, well it’s over now and we will look into the implementation details now with a simple Servlet based web application.
JNDI Configuration for DataSource – server.xml
Add below code in the tomcat server.xml file. The code should be added in the
GlobalNamingResourceselement. Also make sure that database driver is present in the tomcat lib directory, so in this case mysql jdbc jar needs to be present in the tomcat lib.
Here we are creating JNDI context with name as
jdbc/MyDB which is a type of DataSource. We are passing database configurations in url, username, password and driverClassName attribute. Connection pooling properties are defined in maxActive, maxIdle and minIdle attributes.Resource Link Configuration – context.xml
Add below code in the server context.xml file.
Notice that resource link name is different than global link, we should use the name defined in the resource link in the program to get the resource.
Servlet DataSource JNDI Example
Create a dynamic web application with name JDBCDataSourceTomcat and then create a Servlet with below code.
package com.journaldev.jdbc.datasource;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
@WebServlet("/JDBCDataSourceExample")
public class JDBCDataSourceExample extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Context ctx = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/MyLocalDB");
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select empid, name from Employee");
PrintWriter out = response.getWriter();
response.setContentType("text/html");
out.print("
Employee Details
");
out.print("| Employee ID | "); out.print("Employee Name | "); while(rs.next()) { out.print("
|---|---|
| " + rs.getInt("empid") + " | "); out.print("" + rs.getString("name") + " | "); out.print("
Database Details
"); out.print("Database Product: "+con.getMetaData().getDatabaseProductName()+" "); out.print("Database Driver: "+con.getMetaData().getDriverName()); out.print(""); }catch(NamingException e){ e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { rs.close(); stmt.close(); con.close(); ctx.close(); } catch (SQLException e) { System.out.println("Exception in closing DB resources"); } catch (NamingException e) { System.out.println("Exception in closing Context"); } } } }
Notice that I am using Servlet 3 Annotation based configuration and it will work in Tomcat 7 or higher versions. If you are using lower version of Tomcat then you need to do some modifications to the servlet code, basically removing WebServlet annotation and configuring it in web.xml file.
The part of servlet code that we are interested in are;
ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/MyLocalDB");
This is the way to get the JNDI resources defined to be used by the application. We could have written it in this way too;
ctx = new InitialContext();
Context initCtx = (Context) ctx.lookup("java:/comp/env");
DataSource ds = (DataSource) initCtx.lookup("jdbc/MyLocalDB");
I am also printing some database information to check that which database we are connected.
Now when you will run the application, you will see following output.
Let’s see how easy it is to switch the database server because we are using DataSource. All you need is to change the Database properties. So if we have to switch to Oracle database, my Resource configuration will look like below.
And when we restart the server and run the application, it will connect to Oracle database and produce below result.
That’s all for JNDI configuration and usage in Tomcat, you can define the resource in similar way in context.xml files too.
##############Tomcat : Datasource example 2
1. Get MySQL JDBC Driver
Get JDBC driver here – http://www.mysql.com/products/connector/ , for example,
mysql-connector-java-5.1.9.jar, and copy it to $TOMCAT\lib folder.2. Create META-INF/context.xml
Add a file
META-INF/context.xml into the root of your web application folder, which defines database connection detail :
File : META-INF/context.xml
<Context>
<Resource name="jdbc/mkyongdb" auth="Container" type="javax.sql.DataSource"
maxActive="50" maxIdle="30" maxWait="10000"
username="mysqluser" password="mysqlpassword"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mkyongdb"/>
</Context>
In
web.xml, defines your MySQL datasource again : <resource-ref>
<description>MySQL Datasource example</description>
<res-ref-name>jdbc/mkyongdb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
See a full
web.xml example below :
File : web.xml
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
id="WebApp_ID" version="2.5">
<display-name>MySQL DataSource Example</display-name>
<resource-ref>
<description>MySQL Datasource example</description>
<res-ref-name>jdbc/mkyongdb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
4. Run It
Resource injection (
@Resource) is the easiest way to get the datasource from Tomcat, see below :import javax.annotation.Resource;
public class CustomerBean{
@Resource(name="jdbc/mkyongdb")
private DataSource ds;
public List<Customer> getCustomerList() throws SQLException{
//get database connection
Connection con = ds.getConnection();
//...
Alternatively, you can also get the datasource via context lookup service :
import javax.naming.Context;
import javax.naming.InitialContext;
public class CustomerBean{
private DataSource ds;
public CustomerBean(){
try {
Context ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mkyongdb");
} catch (NamingException e) {
e.printStackTrace();
}
}
public List<Customer> getCustomerList() throws SQLException{
//get database connection
Connection con = ds.getConnection();
//...2) Spring JNDI Datasource
Spring Bean Configuration
There are two ways through which we can JNDI lookup and wire it to the Controller DataSource, my spring bean configuration file contains both of them but one of them is commented. You can switch between these and the response will be the same.
- Using jee namespace tag to perform the JNDI lookup and configure it as a Spring Bean. We also need to include jee namespace and schema definition in this case.
- Creating a bean of type
org.springframework.jndi.JndiObjectFactoryBeanby passing the JNDI context name. jndiName is a required parameter for this configuration.
My spring bean configuration file looks like below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| <beans:beans xmlns="http://www.springframework.org/schema/mvc" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <annotation-driven /> <resources mapping="/resources/**" location="/resources/" /> <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <beans:property name="prefix" value="/WEB-INF/views/" /> <beans:property name="suffix" value=".jsp" /> </beans:bean> <beans:bean class="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter"> <beans:property name="messageConverters"> <beans:list> <beans:ref bean="jsonMessageConverter" /> </beans:list> </beans:property> </beans:bean> <beans:bean id="jsonMessageConverter" class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter"> </beans:bean> <beans:bean id="dbDataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> <beans:property name="jndiName" value="java:comp/env/jdbc/MyLocalDB"/> </beans:bean> <context:component-scan base-package="com.journaldev.spring.jdbc.controller" /></beans:beans> |
Tomcat JNDI Configuration
Now that we are done with our project, the final part is to do the JNDI configuration in Tomcat container to create the JNDI resource.
1
2
3
4
5
6
7
8
9
10
11
12
13
| <Resource name="jdbc/TestDB" global="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/TestDB" username="pankaj" password="pankaj123" maxActive="100" maxIdle="20" minIdle="5" maxWait="10000"/> |
Add above configuration in the GlobalNamingResources section of the server.xml file.
1
2
3
4
| <ResourceLink name="jdbc/MyLocalDB" global="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" /> |
We also need to create the Resource Link to use the JNDI configuration in our application, best way to add it in the server context.xml file.
Notice that ResourceLink name should be matching with the JNDI context name we are using in our application. Also make sure MySQL jar is present in the tomcat lib directory, otherwise tomcat will not be able to create the MySQL database connection pool.
Running the Spring JNDI Sample Project
Our project and server configuration is done and we are ready to test it. Export the project as WAR file and place it in the tomcat deployment directory.
The JSON response for the Rest call is shown in the below image.
That’s all for the Spring integration with servlet container JNDI context, download the sample project from the below link and play around with it to learn more.
#########################
3) Datasource in Spring
Programmatic transaction management approach allows you to manage the transaction with the help of programming in your source code. That gives you extreme flexibility, but it is difficult to maintain.
Before we begin, it is important to have at least two database tables on which we can perform various CRUD operations with the help of transactions. Let us take Student table, which can be created in MySQL TEST database with the following DDL:
CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID) );
Second table is Marks in which we will maintain marks for students based on years. Here SID is the foreign key for Student table.
CREATE TABLE Marks( SID INT NOT NULL, MARKS INT NOT NULL, YEAR INT NOT NULL );
Let us use PlatformTransactionManager directly to implement programmatic approach to implement transactions. To start a new transaction you need to have a instance of TransactionDefinition with the appropriate transaction attributes. For this example we will simply create an instance ofDefaultTransactionDefinition to use the default transaction attributes.
Once the TransactionDefinition is created, you can start your transaction by calling getTransaction() method, which returns an instance ofTransactionStatus. The TransactionStatus objects helps in tracking the current status of the transaction and finally, if everything goes fine, you can usecommit() method of PlatformTransactionManager to commit the transaction, otherwise you can use rollback() to rollback the complete operation.
Now let us write our Spring JDBC application which will implement simple operations on Student and Marks tables. Let us have working Eclipse IDE in place and follow the following steps to create a Spring application:
| Step | Description |
|---|---|
| 1 | Create a project with a name SpringExample and create a packagecom.tutorialspoint under the src folder in the created project. |
| 2 | Add required Spring libraries using Add External JARs option as explained in the Spring Hello World Example chapter. |
| 3 | Add Spring JDBC specific latest libraries mysql-connector-java.jar,org.springframework.jdbc.jar andorg.springframework.transaction.jar in the project. You can download required libraries if you do not have them already. |
| 4 | Create DAO interface StudentDAO and list down all the required methods. Though it is not required and you can directly write StudentJDBCTemplateclass, but as a good practice, let's do it. |
| 5 | Create other required Java classes StudentMarks, StudentMarksMapper,StudentJDBCTemplate and MainApp under the com.tutorialspoint package. You can create rest of the POJO classes if required. |
| 6 | Make sure you already created Student and Marks tables in TEST database. Also make sure your MySQL server is working fine and you have read/write access on the database using the give username and password. |
| 7 | Create Beans configuration file Beans.xml under the src folder. |
| 8 | The final step is to create the content of all the Java files and Bean Configuration file and run the application as explained below. |
Following is the content of the Data Access Object interface fileStudentDAO.java:
package com.tutorialspoint; import java.util.List; import javax.sql.DataSource; public interface StudentDAO { /** * This is the method to be used to initialize * database resources ie. connection. */ public void setDataSource(DataSource ds); /** * This is the method to be used to create * a record in the Student and Marks tables. */ public void create(String name, Integer age, Integer marks, Integer year); /** * This is the method to be used to list down * all the records from the Student and Marks tables. */ public List<StudentMarks> listStudents(); }
Following is the content of the StudentMarks.java file:
package com.tutorialspoint; public class StudentMarks { private Integer age; private String name; private Integer id; private Integer marks; private Integer year; private Integer sid; public void setAge(Integer age) { this.age = age; } public Integer getAge() { return age; } public void setName(String name) { this.name = name; } public String getName() { return name; } public void setId(Integer id) { this.id = id; } public Integer getId() { return id; } public void setMarks(Integer marks) { this.marks = marks; } public Integer getMarks() { return marks; } public void setYear(Integer year) { this.year = year; } public Integer getYear() { return year; } public void setSid(Integer sid) { this.sid = sid; } public Integer getSid() { return sid; } }
Following is the content of the StudentMarksMapper.java file:
package com.tutorialspoint; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class StudentMarksMapper implements RowMapper<StudentMarks> { public StudentMarks mapRow(ResultSet rs, int rowNum) throws SQLException { StudentMarks studentMarks = new StudentMarks(); studentMarks.setId(rs.getInt("id")); studentMarks.setName(rs.getString("name")); studentMarks.setAge(rs.getInt("age")); studentMarks.setSid(rs.getInt("sid")); studentMarks.setMarks(rs.getInt("marks")); studentMarks.setYear(rs.getInt("year")); return studentMarks; } }
Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO:
package com.tutorialspoint; import java.util.List; import javax.sql.DataSource; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.TransactionDefinition; import org.springframework.transaction.TransactionStatus; import org.springframework.transaction.support.DefaultTransactionDefinition; public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; private PlatformTransactionManager transactionManager; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcTemplateObject = new JdbcTemplate(dataSource); } public void setTransactionManager( PlatformTransactionManager transactionManager) { this.transactionManager = transactionManager; } public void create(String name, Integer age, Integer marks, Integer year){ TransactionDefinition def = new DefaultTransactionDefinition(); TransactionStatus status = transactionManager.getTransaction(def); try { String SQL1 = "insert into Student (name, age) values (?, ?)"; jdbcTemplateObject.update( SQL1, name, age); // Get the latest student id to be used in Marks table String SQL2 = "select max(id) from Student"; int sid = jdbcTemplateObject.queryForInt( SQL2 ); String SQL3 = "insert into Marks(sid, marks, year) " + "values (?, ?, ?)"; jdbcTemplateObject.update( SQL3, sid, marks, year); System.out.println("Created Name = " + name + ", Age = " + age); transactionManager.commit(status); } catch (DataAccessException e) { System.out.println("Error in creating record, rolling back"); transactionManager.rollback(status); throw e; } return; } public List<StudentMarks> listStudents() { String SQL = "select * from Student, Marks where Student.id=Marks.sid"; List <StudentMarks> studentMarks = jdbcTemplateObject.query(SQL, new StudentMarksMapper()); return studentMarks; } }
Now let us move with the main application file MainApp.java, which is as follows:
package com.tutorialspoint; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.tutorialspoint.StudentJDBCTemplate; public class MainApp { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml"); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate"); System.out.println("------Records creation--------" ); studentJDBCTemplate.create("Zara", 11, 99, 2010); studentJDBCTemplate.create("Nuha", 20, 97, 2010); studentJDBCTemplate.create("Ayan", 25, 100, 2011); System.out.println("------Listing all the records--------" ); List<StudentMarks> studentMarks = studentJDBCTemplate.listStudents(); for (StudentMarks record : studentMarks) { System.out.print("ID : " + record.getId() ); System.out.print(", Name : " + record.getName() ); System.out.print(", Marks : " + record.getMarks()); System.out.print(", Year : " + record.getYear()); System.out.println(", Age : " + record.getAge()); } } }
Following is the configuration file Beans.xml:
xml version="1.0" encoding="UTF-8"?>xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd "> id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> name="driverClassName" value="com.mysql.jdbc.Driver"/> name="url" value="jdbc:mysql://localhost:3306/TEST"/> name="username" value="root"/> name="password" value="password"/> id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> name="dataSource" ref="dataSource" /> id="studentJDBCTemplate" class="com.tutorialspoint.StudentJDBCTemplate"> name="dataSource" ref="dataSource" /> name="transactionManager" ref="transactionManager" />
Once you are done with creating source and bean configuration files, let us run the application. If everything is fine with your application, this will print the following message:
------Records creation-------- Created Name = Zara, Age = 11 Created Name = Nuha, Age = 20 Created Name = Ayan, Age = 25 ------Listing all the records-------- ID : 1, Name : Zara, Marks : 99, Year : 2010, Age : 11 ID : 2, Name : Nuha, Marks : 97, Year : 2010, Age : 20 ID : 3, Name : Ayan, Marks : 100, Year : 2011, Age : 25
No comments:
Post a Comment