Spring JDBCTemplate 简单使用
Spring JDBCTemplate
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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-3.2.xsd
http://www.springframework.org/schema/p
http://www.springframework.org/schema/p/spring-p-3.2.xsd">
<description>Spring-初始 </description>
<!-- 最快捷和最简单的方式:有两种方法：第一bean注入，第二种是通过context引入 -->
<context:property-placeholder location="classpath:conf/db.properties" file-encoding="utf-8"/>
<!-- 通过数据源获取连接 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
p:driverClassName="com.mysql.jdbc.Driver"
p:url="jdbc:mysql://localhost:3306/hrsystem"
p:username="root"
p:password="123"
/>
<!-- 通过配置文件 -->
<bean id="sessionFactory1" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
</props>
</property>
<property name="mappingLocations">
<list>
<value>model/EmpTable.hbm.xml</value>
</list>
</property>
</bean>
<!-- 通过注解 -->
<bean id="sessionFactory2" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
</props>
</property>
<property name="annotatedClasses">
<list>
<value>model.UserTable</value>
</list>
</property>
<!-- 扫描包下所有类 -->
<!-- <property name="packagesToScan"> -->
<!-- <list> -->
<!-- <value>model</value> -->
<!-- </list> -->
<!-- </property> -->
</bean>
<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate"
p:dataSource-ref="dataSource"></bean>
</beans>
EmpTable.hbm.xml
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="model.EmpTable" table="emp_table" catalog="hrsystem">
<id name="empId" type="java.lang.Integer">
<column name="emp_id" />
<generator class="increment" />
</id>
<many-to-one name="empTable" class="model.EmpTable" fetch="select">
<column name="mgr_id" />
</many-to-one>
<property name="empType" type="java.lang.Integer">
<column name="emp_type" />
</property>
<property name="empName" type="java.lang.String">
<column name="emp_name" length="50" not-null="true" unique="true" />
</property>
<property name="empPass" type="java.lang.String">
<column name="emp_pass" length="50" not-null="true" />
</property>
<property name="empSalary" type="java.lang.Double">
<column name="emp_salary" precision="22" scale="0" not-null="true" />
</property>
<property name="deptName" type="java.lang.String">
<column name="dept_name" length="50" />
</property>
</class>
</hibernate-mapping>
EmpTable.java
package model;
import java.util.HashSet;
import java.util.Set;
/**
* EmpTable entity. @author MyEclipse Persistence Tools
*/
public class EmpTable implements java.io.Serializable {
// Fields
private Integer empId;
private EmpTable empTable;
private Integer empType;
private String empName;
private String empPass;
private Double empSalary;
private String deptName;
// Constructors
/** default constructor */
public EmpTable() {
}
/** minimal constructor */
public EmpTable(String empName, String empPass, Double empSalary) {
this.empName = empName;
this.empPass = empPass;
this.empSalary = empSalary;
}
/** full constructor */
public EmpTable(EmpTable empTable, Integer empType, String empName,
String empPass, Double empSalary, String deptName) {
this.empTable = empTable;
this.empType = empType;
this.empName = empName;
this.empPass = empPass;
this.empSalary = empSalary;
this.deptName = deptName;
}
// Property accessors
public Integer getEmpId() {
return this.empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public EmpTable getEmpTable() {
return this.empTable;
}
public void setEmpTable(EmpTable empTable) {
this.empTable = empTable;
}
public Integer getEmpType() {
return this.empType;
}
public void setEmpType(Integer empType) {
this.empType = empType;
}
public String getEmpName() {
return this.empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public String getEmpPass() {
return this.empPass;
}
public void setEmpPass(String empPass) {
this.empPass = empPass;
}
public Double getEmpSalary() {
return this.empSalary;
}
public void setEmpSalary(Double empSalary) {
this.empSalary = empSalary;
}
public String getDeptName() {
return this.deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
}
UserTable.java
package model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
//import org.hibernate.annotations.Table;
@Entity
@Table(name="UserTable")
public class UserTable {
private int id;
private String username;
private int age;
public UserTable(){}
public UserTable(int id, String username, int age) {
super();
this.id = id;
this.username = username;
this.age = age;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "Id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name = "UserName")
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Column(name = "Age")
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
1、简单查询
import java.util.List;
import model.EmpTable;
import model.UserTable;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class TestClass {
@Autowired
private SessionFactory sessionFactory1;
@Autowired
private SessionFactory sessionFactory2;
@Test//实体使用映射文件
public void test1(){
System.out.println("============Test1-Start============");
Session session = sessionFactory1.openSession();
List<EmpTable> emps = session.createQuery("from EmpTable").list();
for (EmpTable emp : emps) {
System.out.println(emp.getEmpName() +"--" + emp.getEmpSalary());
}
System.out.println("============Test1-End============");
}
@Test//实体使用注解
public void test2(){
System.out.println("============Test2-Start============");
Session session = sessionFactory2.openSession();
//操作数据库了,CURD查询 sql hql qbc qbe
List<UserTable> users = session.createQuery("from UserTable ").list();
for (UserTable user : users) {
System.out.print(user.getUsername() + "--" + user.getAge());
}
System.out.println("============Test2-End============");
}
}
2、简单查询
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class TestJdbcTemplate {
@Autowired
private JdbcTemplate template;
@Test //SQL查询，使用RowCallbackHandler
public void test1() {
template.query("select * from UserTable", new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
System.out.println(rs.getString("UserName") + "---" + rs.getInt("Age"));
}
});
}
@Test //Insert演示
public void test2(){
final String sql="insert into UserTable (UserName, Age) VALUES (?, ?)";
//执行Insert，并返回主键
KeyHolder keyHolder=new GeneratedKeyHolder();
template.update(new PreparedStatementCreator(){
@Override
public PreparedStatement createPreparedStatement(Connection arg0)
throws SQLException {
PreparedStatement ps=arg0.prepareStatement(sql);
ps.setString(1, "JdbcTemplate2");
ps.setInt(2, 2);
return ps;
}
},keyHolder);
System.out.println("主键："+keyHolder.getKey().intValue());
}
@Test //Insert演示
public void test3(){
final String sql="insert into UserTable (UserName, Age) VALUES (?, ?)";
template.update(sql,"JdbcTemplate3",12);
}
@Test //Insert演示
public void test4(){
final String sql="insert into UserTable (UserName, Age) VALUES (?, ?)";
template.update(sql,new PreparedStatementSetter(){
@Override
public void setValues(PreparedStatement arg0) throws SQLException {
arg0.setString(1, "JdbcTemplate4");
arg0.setInt(2, 13);
}});
}
@Test //Insert演示
public void test5(){
final String sql="insert into UserTable (UserName, Age) VALUES (?, ?)";
template.update(sql,new Object[]{"JdbcTemplate5",14},new int[] {Types.VARCHAR,Types.INTEGER});
}
}
3、查询结果以List\Map形式返回，存储过程调用
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import model.UserTable;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class TestJdbcTemplateSelect {
@Autowired
private JdbcTemplate template;
@Test
//查询参数使用：PreparedStatementCreator，返回结果使用：RowCallbackHandler
public void test1(){
final String sql="select * from UserTable where UserName <> ? ";
final List<UserTable> list=new LinkedList<UserTable>();
template.query(new PreparedStatementCreator(){
@Override
public PreparedStatement createPreparedStatement(Connection arg0)
throws SQLException {
PreparedStatement ps=arg0.prepareStatement(sql);
ps.setString(1, "JdbcTemplate3");
return ps;
}
}, new RowCallbackHandler(){
@Override
public void processRow(ResultSet arg0) throws SQLException {
UserTable user=new UserTable();
user.setId(arg0.getInt("Id"));
user.setUsername(arg0.getString("UserName"));
user.setAge(arg0.getInt("Age"));
list.add(user);
}});
for(UserTable item : list){
System.out.println(item.getId()+"--"+item.getUsername()+"--"+item.getAge());
}
}
@Test
//查询参数直接传递，返回结果使用：RowMapper
public void test2(){
final String sql="select * from UserTable where UserName<>? ";
List<UserTable> list = template.query(sql, new RowMapper<UserTable>(){
@Override
public UserTable mapRow(ResultSet arg0, int arg1)
throws SQLException {
UserTable user=new UserTable();
user.setId(arg0.getInt("Id"));
user.setUsername(arg0.getString("UserName"));
user.setAge(arg0.getInt("Age"));
return user;
}
}, "JdbcTemplate3");
for(UserTable item : list){
System.out.println(item.getId()+"**"+item.getUsername()+"**"+item.getAge());
}
}
@Test
//返回Map
public void test3(){
final String sql="select * from UserTable where Id=53 ";
Map<String,Object>map=template.queryForMap(sql);
for(String key : map.keySet()){
System.out.println(key+"+++"+map.get(key));
}
}
@Test
//返回List
public void test4() {
final String sql = "select * from UserTable ";
List<UserTable> list = (List<UserTable>) template.query(sql,
new ResultSetExtractor() {
@Override
public Object extractData(ResultSet rs)
throws SQLException, DataAccessException {
List<UserTable> list = new LinkedList<UserTable>();
while (rs.next()) {
UserTable user = new UserTable();
user.setId(rs.getInt("Id"));
user.setUsername(rs.getString("UserName"));
user.setAge(rs.getInt("Age"));
list.add(user);
}
return list;
}
});
for(UserTable item : list){
System.out.println(item.getId()+"*-*"+item.getUsername()+"*-*"+item.getAge());
}
}
@Test
//调用存储过程
public void Test5(){
/* drop PROCEDURE if EXISTS UserSearch;
create PROCEDURE UserSearch(IN parId INTEGER,OUT parSum INTEGER)
BEGIN
select count(*) into parSum from UserTable where Id > parId;
select * from UserTable where Id>parId;
select * from emp_table;
end;
*/
List<SqlParameter> sqlParameters = new ArrayList<SqlParameter>();
sqlParameters.add(new SqlParameter("parId", Types.INTEGER));
sqlParameters.add(new SqlOutParameter("parSum", Types.INTEGER));
Map<String, Object> map = template.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con)
throws SQLException {
CallableStatement statement = con.prepareCall("{call UserSearch(?,?)}");
statement.setInt(1, 17);
return statement;
}
}, sqlParameters);
System.out.println(map.get("parSum"));
}
}
