Spring_ using JdbcTemplate and jdbcdaosupport-codes

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.