Spring_ using JdbcTemplate and jdbcdaosupport-codes

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 ">

<context:component-scan base-package= "Om.hy.spring.JdbcTemplate" >
</context:component-scan>

<!--import resource file-->
<context:property-placeholder location= "classpath:db.properties"/>

<! --Configure C3P0 data source-->
<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's jdbctemplate-->
<bean Id= "JdbcTemplate"
class= "org.springframework.jdbc.core.JdbcTemplate";
<property name= "DataSource" ref= "DataSource" ></PROPERTY>
</bean>

</beans>

Db.properties

Jdbc.user=sems
Jdbc.password=iotcomm
Jdbc.driverclass=com.mysql.jdbc.driver
jdbc.jdbcurl=jdbc:mysql://61.131.64.118\:3204/test?useunicode=true&characterencoding=utf-8& Zerodatetimebehavior=converttonull
Jdbc.initpoolsize=5
jdbc.maxpoolsize=10

Department.java

Package COM.ATGUIGU.SPRING.JDBC;

public class Department {

Private Integer ID;
private String name;

Public Integer getId () {
return ID;
}

public void SetId (Integer id) {
This.id = ID;
}

Public String GetName () {
return name;
}

public void SetName (String name) {
THIS.name = name;
}

@Override
Public String toString () {
Return "Department [id=" + ID + ", name=" + name + "]";
}

}

Departmentdao.java

Package COM.ATGUIGU.SPRING.JDBC;

Import Javax.sql.DataSource;

Import org.springframework.beans.factory.annotation.Autowired;
Import Org.springframework.jdbc.core.BeanPropertyRowMapper;
Import Org.springframework.jdbc.core.RowMapper;
Import Org.springframework.jdbc.core.support.JdbcDaoSupport;
Import Org.springframework.stereotype.Repository;

/**
* Jdbcdaosupport is not recommended, but it is recommended to use Jdbctempate directly as a member variable of the Dao class
*/
@Repository
public class Departmentdao extends jdbcdaosupport{

@Autowired
public void SetDataSource2 (DataSource DataSource) {
Setdatasource (DataSource);
}

Public Department get (Integer ID) {
String sql = "SELECT ID, dept_name name from departments WHERE ID =?";
rowmapper<department> RowMapper = new beanpropertyrowmapper<> (department.class);
Return Getjdbctemplate (). queryForObject (SQL, RowMapper, id);
}

}

Employee.java

Package COM.ATGUIGU.SPRING.JDBC;

public class Employee {

Private Integer ID;
Private String LastName;
Private String Email;

Private Integer Dpetid;

Public Integer getId () {
return ID;
}

public void SetId (Integer id) {
This.id = ID;
}

Public String Getlastname () {
return lastName;
}

public void Setlastname (String lastName) {
This.lastname = LastName;
}

Public String Getemail () {
return email;
}

public void Setemail (String email) {
This.email = email;
}

Public Integer Getdpetid () {
return dpetid;
}

public void Setdpetid (Integer dpetid) {
This.dpetid = Dpetid;
}

@Override
Public String toString () {
Return "Employee [id=" + ID + ", lastname=" + LastName + ", email="
+ email + ", dpetid=" + Dpetid + "]";
}

}

Employeedao.java

Package COM.ATGUIGU.SPRING.JDBC;

Import org.springframework.beans.factory.annotation.Autowired;
Import Org.springframework.jdbc.core.BeanPropertyRowMapper;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.jdbc.core.RowMapper;
Import Org.springframework.stereotype.Repository;

@Repository
public class EmployeeDAO {

@Autowired
Private JdbcTemplate JdbcTemplate;

Public Employee get (Integer ID) {
String sql = "SELECT ID, last_name lastName, email from employees WHERE id =?";
rowmapper<employee> RowMapper = new beanpropertyrowmapper<> (employee.class);
Employee employee = jdbctemplate.queryforobject (sql, RowMapper, id);

return employee;
}
}

Jdbctest.java

Package COM.ATGUIGU.SPRING.JDBC;

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 Departmentdao Departmentdao;
Private Namedparameterjdbctemplate namedparameterjdbctemplate;

{
CTX = new Classpathxmlapplicationcontext ("Applicationcontext.xml");
JdbcTemplate = (jdbctemplate) ctx.getbean ("JdbcTemplate");
EmployeeDAO = Ctx.getbean (Employeedao.class);
Departmentdao = Ctx.getbean (Departmentdao.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 employees (last_name, email, dept_id)"
+ "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 employees (last_name, email, dept_id) VALUES (: Ln,:email,:d eptid)";

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

Namedparameterjdbctemplate.update (SQL, PARAMMAP);
}

@Test
public void Testdepartmentdao () {
System.out.println (Departmentdao.get (1));
}

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

/**
* Get the value of a single column, or do a statistical query
* Using queryForObject (String sql, class<long> requiredtype)
*/
@Test
public void TestQueryForObject2 () {
String sql = "SELECT count (ID) from employees";
Long Count = Jdbctemplate.queryforobject (sql, Long.class);

System.out.println (count);
}

/**
* Find the collection of entity classes
* Note that calling is not a queryForList method
*/
@Test
public void Testqueryforlist () {
String sql = "SELECT ID, last_name lastName, email from employees WHERE ID >?";
rowmapper<employee> RowMapper = new beanpropertyrowmapper<> (employee.class);
List<employee> employees = jdbctemplate.query (sql, rowmapper,5);

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 ID, last_name lastName, email, dept_id as \" Department.id\ "from employees WHERE id =?";
rowmapper<employee> RowMapper = new beanpropertyrowmapper<> (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 Testbatchupdate () {
String sql = "INSERT into employees (last_name, email, dept_id) VALUES (?,?,?)";

list<object[]> Batchargs = new arraylist<> ();

Batchargs.add (New object[]{"AA", "[email protected]", 1});
Batchargs.add (New object[]{"BB", "[email protected]", 2});
Batchargs.add (New object[]{"CC", "[email protected]", 3});
Batchargs.add (New object[]{"DD", "[email protected]", 3});
Batchargs.add (New object[]{"EE", "[email protected]", 2});

Jdbctemplate.batchupdate (SQL, Batchargs);
}

/**
* Perform INSERT, UPDATE, DELETE
*/
@Test
public void Testupdate () {
String sql = "UPDATE employees SET last_name =?" WHERE id =? ";
Jdbctemplate.update (SQL, "Jack", 5);
}

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

}

Spring_ using JdbcTemplate and jdbcdaosupport-codes