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