SpringJDBC下操作数据源dataSource
1）SpringJDBC下操作数据源：
为了学习TransactionManager，我们先学习一下SpringJDBC下操作数据源：org.apache.commons.dbcp.BasicDataSource。做以下实验时，要先导几个包。commons-collections-3.2.1.jar，commons-dbcp-1.4.jar，commons-pool-1.5.4.jar，mysql-connector-java-3.1.10-bin.jar，spring-jdbc-3.0.5.RELEASE.jar，spring-orm-3.0.5.RELEASE.jar，spring-tx-3.0.5.RELEASE.jar，
例 2.2.1
先在web.xml中加入下面的语句：
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
<property name="driverClassName"
value="com.mysql.jdbc.Driver"></property>
<property name="url"
value="jdbc:mysql://localhost:3306/test"></property>
<property name="username"
value="root"></property>
<property name="password"
value="1234"></property>
</bean>
<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg ref="dataSource" />
</bean>
package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import com.NiutDAO;
import service.interfac.ILoginService;
import org.springframework.jdbc.core.RowCallbackHandler;
public class LoginServiceImpl implements ILoginService {
@Resource
private JdbcTemplate jt;
public void login() {
System.out.println("LoginServiceImpl");
String sql = "SELECT COUNT(id) FROM register";
Object[] params = new Object[] {};
jt.query(sql, params, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
System.out.println(rs.getInt(1)) ;
}
});
}
}
运行结果：
LoginServiceImpl
10
after loginServic.login()
后记：processRow只能处理一行，想处理多行参考下段。
final List objList = new ArrayList();
jdbcTemplate.query(listSql, new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
Map row = new HashMap();
row.put(rs.getInt("id"), rs.getString("name"));
objList.add(row);
}
});
例 2.2.1_1:（simpleJdbcTemplate的项目【比JdbcTemplate更好使用】）：
TeacherPupil.java:
package com;
public class TeacherPupil {
public String toString() {
return "pname:"+pname+"tid:"+tid+"\nname:"+name+"\nage:"+age;
}
private String pname;
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
private Integer tid;
private String name;
private Integer age;
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
LoginServiceImpl.java:
package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import service.interfac.ILoginService;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import com.Register;
import com.TeacherPupil;
public class LoginServiceImpl implements ILoginService {
@Resource
private JdbcTemplate jt1;
public void setJt1(JdbcTemplate jt) {
this.jt1 = jt;
}
@Resource
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
this.simpleJdbcTemplate = simpleJdbcTemplate;
}
public void login() {
System.out.println("LoginServiceImpl1111");
String sql = "SELECT COUNT(id) FROM register";
Object[] params = new Object[] {};
jt1.query(sql, params, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
System.out.println(rs.getInt(1)) ;
}
});
int res=this.simpleJdbcTemplate.queryForInt(sql);
System.out.println("res is "+res) ;
//增
Register reg = new Register();
reg.setId(61);
reg.setName("abc");
reg.setAge(28);
String sqlInsert = "insert into register(id,name,age) values(:id,:name,:age)";
simpleJdbcTemplate.update(sqlInsert.toString(), new BeanPropertySqlParameterSource(reg));
System.out.println("insert ok ") ;
///改
StringBuffer sqlgai = new StringBuffer();
sqlgai.append("update register set name=? where id=?");
simpleJdbcTemplate.update(sqlgai.toString(),"abc1", 61);
System.out.println("改 ok ") ;
///改2
StringBuffer sqlgai1 = new StringBuffer();
sqlgai1.append("update register set name=? where id=?");
simpleJdbcTemplate.update(sqlgai1.toString(),new Object[] { "abc2", 61 });
System.out.println("改 ok1 ") ;
///批量改
StringBuffer sqlgaibatch = new StringBuffer();
sqlgaibatch.append("update register set name=? where id=?");
List<Object[]> parameters = new ArrayList<Object[]>();
parameters.add(new Object[] { "qwe1",1 });
parameters.add(new Object[] { "ert2",2 });
simpleJdbcTemplate.batchUpdate(sqlgaibatch.toString(), parameters);
System.out.println("批量改 ok ") ;
/查询
String sqldan = "select * from register where id=?";
Register regi=simpleJdbcTemplate.queryForObject(sqldan,new BeanPropertyRowMapper(Register.class),1);
System.out.println("一个 regi "+ regi.getName()) ;
///查询多个
String sqlselec = "select * from register";
List<Register> regis = simpleJdbcTemplate.query(sqlselec, new BeanPropertyRowMapper(Register.class));
for(Object obj :regis){
System.out.println(obj);
}
System.out.println("成功查询多个");
/
/*一对多，专门做一个类TeacherPupil，接收.用底下的as 别名方法，就可以解决teacher和pupil表中都有同名的字段name的方法。这样就可以从不同的表中同时取值了。*/
String sqlotm = "select pupil.name as pname, teacher.name,pupil.tid from pupil,teacher where tid=teacher.id and teacher.name='qixy';";
List<TeacherPupil> teaPup = simpleJdbcTemplate.query(sqlotm, new BeanPropertyRowMapper(TeacherPupil.class));
for(Object obj :teaPup){
System.out.println(obj);
}
System.out.println("成功一对多");
///查询多个带条件
String sqlselecc = "select * from register where id<:id";
List<Register> regisc = simpleJdbcTemplate.query(sqlselecc, new BeanPropertyRowMapper(Register.class), new BeanPropertySqlParameterSource(reg));
for(Object obj :regisc){
System.out.println(obj);
}
//删
String sqldel = " delete from register where id=?";
simpleJdbcTemplate.update(sqldel, 61);
}
public static void main(String[] args)
{
ClassPathXmlApplicationContext cp= new ClassPathXmlApplicationContext("applicationContext.xml");
ILoginService ls=(ILoginService)cp.getBean("loginService");
ls.login();
}
}
applicationContext.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="loginService" class="service.LoginServiceImpl" >
<property name="simpleJdbcTemplate">
<ref bean="simpleJdbcTemplate" />
</property>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
<property name="driverClassName"
value="com.mysql.jdbc.Driver"></property>
<property name="url"
value="jdbc:mysql://localhost:3306/test"></property>
<property name="username"
value="root"></property>
<property name="password"
value="1234"></property>
</bean>
<bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
<constructor-arg ref="dataSource" />
</bean>
</beans>
更多内容请见原文，文章转载自：https://blog.csdn.net/qq_44591615/article/details/109206371
