Spring_ using Namedparameterjdbctemplate

Applicationcontext.xml

<?xml version= "1.0" encoding= "UTF-8"?>
<beans xmlns= "Http://www.springframework.org/schema/beans"
Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance"
xmlns:context= "Http://www.springframework.org/schema/context"
xsi:schemalocation= "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-4.1.xsd ">

<!--importing resource files--
<context:property-placeholder location= "Classpath:db.properties"/>
<context:component-scan base-package= "Om.hy.spring.JdbcTemplate" >
</context:component-scan>

<!--configuring C3P0 data Sources--
<bean id= "DataSource"
class= "Com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name= "user" value= "${jdbc.user}" ></property>
<property name= "Password" value= "${jdbc.password}" ></property>
<property name= "Jdbcurl" value= "${jdbc.jdbcurl}" ></property>
<property name= "Driverclass" value= "${jdbc.driverclass}" ></property>
<property name= "initialpoolsize" value= "${jdbc.initpoolsize}" ></property>
<property name= "maxpoolsize" value= "${jdbc.maxpoolsize}" ></property>
</bean>

<!--Configure Spring JdbcTemplate--
<bean id= "JdbcTemplate"
class= "Org.springframework.jdbc.core.JdbcTemplate" >
<property name= "DataSource" ref= "DataSource" ></property>
</bean>

<!--configuration Namedparameterjdbctemplate, the object can make the appliance name parameter, which has no parameterless constructor, so you must specify parameters for its constructor--
<bean id= "Namedparameterjdbctemplate"
class= "Org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" >
<constructor-arg ref= "DataSource" ></constructor-arg>
</bean>

</beans>

Jdbctest.java

Package om.hy.spring.JdbcTemplate;

Import static org.junit.assert.*;

Import java.sql.SQLException;
Import java.util.ArrayList;
Import Java.util.HashMap;
Import java.util.List;
Import Java.util.Map;

Import Javax.sql.DataSource;

Import Org.junit.Test;
Import Org.springframework.context.ApplicationContext;
Import Org.springframework.context.support.ClassPathXmlApplicationContext;
Import Org.springframework.jdbc.core.BeanPropertyRowMapper;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.jdbc.core.RowMapper;
Import Org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
Import Org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
Import Org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class Jdbctest {

Private ApplicationContext CTX = null;
Private JdbcTemplate JdbcTemplate;
Private EmployeeDAO EmployeeDAO;
Private Namedparameterjdbctemplate namedparameterjdbctemplate;
{
CTX = new Classpathxmlapplicationcontext ("Applicationcontext.xml");
JdbcTemplate = (jdbctemplate) ctx.getbean ("JdbcTemplate");
EmployeeDAO = (EmployeeDAO) Ctx.getbean (Employeedao.class);
Namedparameterjdbctemplate = Ctx.getbean (Namedparameterjdbctemplate.class);
}

/**
* You can use the update (String sql, Sqlparametersource Paramsource) method for updating operations when using named parameters
* 1. Parameter names in SQL statements are consistent with class properties!
* 2. Use Sqlparametersource's Beanpropertysqlparametersource implementation class as a parameter.
*/
@Test
public void TestNamedParameterJdbcTemplate2 () {
String sql = "INSERT into employee (lastName, email, dpetid)"
+ "VALUES (: Lastname,:email,:d petid)";

Employee Employee = new Employee ();
Employee.setlastname ("XYZ");
Employee.setemail ("[email protected]");
Employee.setdpetid (3);

Sqlparametersource Paramsource = new Beanpropertysqlparametersource (employee);
Namedparameterjdbctemplate.update (SQL, Paramsource);
}

/**
* You can name the parameter.
* 1. Benefits: If there are multiple parameters, then do not go to the corresponding position, directly corresponding to the parameter name, easy to maintain
* 2. Cons: More trouble.
*/
@Test
public void Testnamedparameterjdbctemplate () {
String sql = "INSERT into employee (lastName, email, dpetid) VALUES (: Ln,:email,:d eptid)";

map<string, object> parammap = new hashmap<string, object> ();
Parammap.put ("ln", "FF");
Parammap.put ("Email", "[email protected]");
Parammap.put ("DeptID", 2);

Namedparameterjdbctemplate.update (SQL, PARAMMAP);
}

@Test
public void Testemployeedao () {
System.out.println (Employeedao.getemployee (1));
}


@Test
public void TestQueryForObject2 () {
String sql = "SELECT COUNT (*) from employee";
Long Count = Jdbctemplate.queryforobject (sql, Long.class);
System.out.println (count);
}


@Test
public void Testqueryforlist () {
String sql = "SELECT * from employee where ID >?";
rowmapper<employee> RowMapper = new beanpropertyrowmapper<employee> (employee.class);
List<employee> employees = jdbctemplate.query (sql, RowMapper, 2);
SYSTEM.OUT.PRINTLN (employees);
}

/**
* Get a record from the database and actually get a corresponding object
* Note that it is not called queryforobject (String sql, class<employee> requiredtype, Object ... args) Method!
* Instead of calling queryForObject (String sql, rowmapper<employee> rowmapper, Object ... args)
* 1. Where the RowMapper specifies how to map the rows of the result set, the common implementation class is Beanpropertyrowmapper
* 2. Use the alias of a column in SQL to complete the mapping of the column name and the property name of the class. For example Last_Name LastName
* 3. Cascading properties are not supported. JdbcTemplate is a JDBC gadget, not an ORM framework
*/
@Test
public void Testqueryforobject () {
String sql = "SELECT * FROM employee WHERE id =?";
rowmapper<employee> RowMapper = new beanpropertyrowmapper<employee> (employee.class);
Employee employee = jdbctemplate.queryforobject (sql, RowMapper, 1);
SYSTEM.OUT.PRINTLN (employee);
}

/**
* Perform batch update: Batch INSERT, UPDATE, DELETE
* The last parameter is the List type of object[]: Because modifying a record requires an array of object, then multiple objects do not need more than one array of object
*/
@Test
public void Testbatchinsert () {
String sql = "Insert into employee (lastName, email, dpetid)"
+ "VALUES (?,?,?)";
list<object[]> Batchargs = new arraylist<object[]> ();
Batchargs.add (New object[]{"AA", "[email protected]", 1});
Batchargs.add (New object[]{"BB", "[email protected]", 1});
Batchargs.add (New object[]{"CC", "[email protected]", 1});
Batchargs.add (New object[]{"DD", "[email protected]", 1});
Jdbctemplate.batchupdate (SQL, Batchargs);
}

/**
* Perform INSERT, UPDATE, DELETE
*/
@Test
public void Testinsert () {
String sql = "Insert into employee (lastName, email, dpetid)"
+ "VALUES (?,?,?)";
Jdbctemplate.update (SQL, "TOM", "[email protected]", 1);
}

@Test
public void Testdatasource () throws SQLException {
DataSource DataSource = Ctx.getbean (Datasource.class);
System.out.println (Datasource.getconnection ());
}

}

Spring_ using Namedparameterjdbctemplate