spring之JDBC

来源：互联网发布：网络4大粪坑编辑：程序博客网时间：2024/05/29 00:33

spring之JDBC

对于JDBC，我想学过Java的童鞋都应该是啥玩意，我的简单理解就是：访问、操纵数据库的一套API。对于纯生的JDBC，对于我这种java程序员都写烂了！深刻体会到那一个过程的繁琐性。那么这个过程是怎么样的呢，其实很简单：加载数据库驱动driver、获取数据库链接DriverManager.getConnection()、写好数据库语句Statement或者PreparedStatement、然后填充相应的占位符、query数据库得到ResultSet或者update数据库或者批量batch更新操作、完成后关闭resultSet和preparedStatement和connnection。

过程中还需要try-catch-finally将这一过程包裹起来！着实是一件繁琐，蛋疼的事情！后来，涌现出来了jdbcUtils、hibernate以及mybatis之类的想要简化上面这一访问、操作数据库的流程！

本文介绍的也是为了简化应用程序开发过程对数据库访问和操作的流程及简化代码！Spring我就不介绍了，说它是一个优秀的IOC、AOP框架不为过，但这样说又狭隘了点！因为，spring可以用在很多地方（当然了，这只是我个人的说法而已啦！）。它对jdbc的支持是相当不错的！下面主要介绍jdbcTemplate和namedParameterJdbcTemplate

首先，建立一个Java project，建立lib文件夹，放入相应的Jar包，并add to build path（加入类路径），在数据库建一个简单的tb_user表。下面是jar包、项目的目录结构和数据库表。其中jar包下载链接在这里:spring支持jdbc之jar包

下面是User实体类

[csharp] view plain copy

print?

package com.atguigu.jdbc;
public class User {
private Integer id;
private String name;
private String address;
private String lastName;
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;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
@Override
public String toString() {
return “User [id=” + id + “, name=” + name + “, address=” + address
+ ”, lastName=” + lastName + “]”;
}
}

<span style="font-size:18px;">package com.atguigu.jdbc;public class User {    private Integer id;    private String name;    private String address;    private String lastName;    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;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    public String getLastName() {        return lastName;    }    public void setLastName(String lastName) {        this.lastName = lastName;    }    @Override    public String toString() {        return "User [id=" + id + ", name=" + name + ", address=" + address                + ", lastName=" + lastName + "]";    }}</span>

下面是dataBase.properties：需要放置在类路径src下

[java] view plain copy

print?

jdbc.user=root
jdbc.password=123456
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/db_spring
jdbc.initPoolSize=5
jdbc.maxPoolSize=10

<span style="font-size:18px;">jdbc.user=rootjdbc.password=123456jdbc.driverClass=com.mysql.jdbc.Driverjdbc.jdbcUrl=jdbc:mysql://localhost:3306/db_springjdbc.initPoolSize=5jdbc.maxPoolSize=10</span>

下面是spring的配置文件：

[java] view plain copy

print?

<?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.0.xsd”>
<!– 导入数据库配置文件 –>
<context:property-placeholder location=”classpath:dataBase.properties”/>
<!– 配置数据源 –>
<bean id=”dataSource” class=“com.mchange.v2.c3p0.ComboPooledDataSource”>
<property name=”user” value=“{jdbc.user}"></property>  </li><li class="alt">        <property name="password" value="{jdbc.password}”></property>
<property name=”driverClass” value=“{jdbc.driverClass}"></property>  </li><li class="alt">        <property name="jdbcUrl" value="{jdbc.jdbcUrl}”></property>
<property name=”initialPoolSize” value=“{jdbc.initPoolSize}"></property>  </li><li class="">        <property name="maxPoolSize" value="{jdbc.maxPoolSize}”></property>
</bean>
<!– 配置jdbcTemplate –>
<bean id=”jdbcTemplate” class=“org.springframework.jdbc.core.JdbcTemplate”>
<property name=”dataSource” ref=“dataSource”></property>
</bean>
<!– 配置 –>
<bean id=”namedParameterJdbcTemplate” class=“org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate”>
<constructor-arg ref=”dataSource”></constructor-arg>
</bean>
</beans>

<span style="font-size:18px;"><?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.0.xsd">    <!-- 导入数据库配置文件 -->    <context:property-placeholder location="classpath:dataBase.properties"/>    <!-- 配置数据源 -->    <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="driverClass" value="${jdbc.driverClass}"></property>        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>        <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>    </bean>    <!-- 配置jdbcTemplate -->    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource"  ref="dataSource"></property>    </bean>    <!-- 配置  -->    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">        <constructor-arg ref="dataSource"></constructor-arg>    </bean></beans></span>

下面是jdbcTemplate和NamedParameterJdbcTemplate代码

[java] view plain copy

print?

package com.atguigu.jdbc;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
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.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
public class SpringToJDBC {
ApplicationContext ctx=null;
JdbcTemplate jdbcTemplate=null;
NamedParameterJdbcTemplate namedParameterJdbcTemplate=null;
{
ctx=new ClassPathXmlApplicationContext(“spring-jdbc.xml”);
jdbcTemplate=(JdbcTemplate) ctx.getBean(”jdbcTemplate”);
namedParameterJdbcTemplate=
(NamedParameterJdbcTemplate) ctx.getBean(”namedParameterJdbcTemplate”);
}
/*测试增删改*/
@Test
public void testInsert(){
String sql=”insert into tb_user(name,address) values(‘李连杰’,’上海’)”;
jdbcTemplate.execute(sql);
}
//批量更新
@Test
public void batchUpdate(){
String sql=”insert into tb_user(name,address,last_name) values(?,?,?)”;
List<Object[]> batchArgs=new ArrayList<>();
batchArgs.add(new Object[]{“欧阳锋”,“北京”,“欧阳”});
batchArgs.add(new Object[]{“石破天”,“南京”,“狗杂种”});
batchArgs.add(new Object[]{“少林扫地僧”,“天京”,“欧阳”});
batchArgs.add(new Object[]{“乔峰”,“湖南”,“欧阳”});
batchArgs.add(new Object[]{“郭靖”,“湖北”,“欧阳”});
batchArgs.add(new Object[]{“黄蓉”,“北京”,“黄色”});
batchArgs.add(new Object[]{“杨过”,“北京”,“杨氏”});
int[] res=jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(res.length);
}
@Test
public void testUpdate(){
String sql=”update tb_user set name=’稳杰’,address=’南海’ where id=?”;
int res=jdbcTemplate.update(sql,2);
System.out.println(res);
}
@Test
public void testDelete(){
String sql=”delete from tb_user where id=?”;
int res=jdbcTemplate.update(sql,12);
System.out.println(res);
}
//查询一个对象
@Test
public void testQueryOneObject(){
String sql=”select id,name,address,last_name as lastName from tb_user where id=?”;
User user=
jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), 22);
System.out.println(user);
System.out.println(user.getLastName());
}
//查询列表
@Test
public void testQueryList(){
String sql = ”select * from tb_user where address like ’%京%’”;
List<User> userList=
(List<User>) jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));
System.out.println(”查询List: ”);
for (User user : userList) {
System.out.println(user);
}
System.out.println(”数量: ”+userList.size());
}
//查询列表
@Test
public void testQueryListWithArgs(){
String sql = ”select * from tb_user where name=? and address = ?”;
List<User> userList=
(List<User>) jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),new Object[]{“稳杰”,“北京”});
System.out.println(”查询List: ”);
for (User user : userList) {
System.out.println(user);
}
System.out.println(”数量: ”+userList.size());
}
//查询指定的字段
@Test
public void testMap(){
String param=”京”;
String sql = ”select id,name,address from tb_user where address like ’%”+param+“%’ order by id desc ”;
List<User> userList=
(List<User>) jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));
Map<String, Object[]> map = new HashMap<String, Object[]>();
for (User user:userList) {
Object[] userOtherMsg=new Object[2];
userOtherMsg[0]=user.getName();
userOtherMsg[1]=user.getAddress();
map.put(String.valueOf(user.getId()), userOtherMsg );
}
System.out.println(”查询特定的字段: ”);
for (Map.Entry<String, Object[]> entry:map.entrySet()) {
System.out.println(entry.getKey()+” – ” +entry.getValue()[0]+“ – ”+entry.getValue()[1]);
}
System.out.println();
}
//查询数量
@Test
public void testQueryCount(){
String param=”上海”;
String sql=”select Count(*) from tb_user where address like ’%”+param+“%’”;
@SuppressWarnings(“deprecation”)
long count=jdbcTemplate.queryForLong(sql);
System.out.println(count);
}
//查询数量
@Test
public void testQueryObject2(){
String arg=”京”;
String sql=”select count(*) from tb_user where address like ’%”+arg+“%’”;
Long count=jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(count);
}
@Test
public void testJDBC() throws SQLException{
DataSource dataSource=ctx.getBean(DataSource.class);
System.out.println(dataSource.getConnection());
}
@Test
public void testNamedParameterjdbcTemplate(){
String sql=”insert into tb_user(name,address,last_name) values(:name,:addr,:ln)”;
Map<String, Object> paramMap=new HashMap<>();
paramMap.put(”name”, “刘德华”);
paramMap.put(”addr”, “香港”);
paramMap.put(”ln”, “刘德华”);
namedParameterJdbcTemplate.update(sql, paramMap);
}
@Test
public void testNamedParameterjdbcTemplate2(){
String sql=”insert into tb_user(name,address,last_name) values(:name,:address,:lastName)”;
User user=new User();
user.setName(”钟林森”);
user.setAddress(”佛山”);
user.setLastName(”钟”);
//面向对象的SQL
SqlParameterSource paramSource=new BeanPropertySqlParameterSource(user);
namedParameterJdbcTemplate.update(sql,paramSource);
}
}

<span style="font-size:18px;">package com.atguigu.jdbc;import java.sql.SQLException;import java.util.ArrayList;import java.util.Arrays;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;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.namedparam.BeanPropertySqlParameterSource;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.jdbc.core.namedparam.SqlParameterSource;public class SpringToJDBC {    ApplicationContext ctx=null;    JdbcTemplate jdbcTemplate=null;    NamedParameterJdbcTemplate namedParameterJdbcTemplate=null;    {        ctx=new ClassPathXmlApplicationContext("spring-jdbc.xml");        jdbcTemplate=(JdbcTemplate) ctx.getBean("jdbcTemplate");        namedParameterJdbcTemplate=                (NamedParameterJdbcTemplate) ctx.getBean("namedParameterJdbcTemplate");    }    /*测试增删改*/    @Test    public void testInsert(){        String sql="insert into tb_user(name,address) values('李连杰','上海')";        jdbcTemplate.execute(sql);    }    //批量更新    @Test    public void batchUpdate(){        String sql="insert into tb_user(name,address,last_name) values(?,?,?)";        List<Object[]> batchArgs=new ArrayList<>();        batchArgs.add(new Object[]{"欧阳锋","北京","欧阳"});        batchArgs.add(new Object[]{"石破天","南京","狗杂种"});        batchArgs.add(new Object[]{"少林扫地僧","天京","欧阳"});        batchArgs.add(new Object[]{"乔峰","湖南","欧阳"});        batchArgs.add(new Object[]{"郭靖","湖北","欧阳"});        batchArgs.add(new Object[]{"黄蓉","北京","黄色"});        batchArgs.add(new Object[]{"杨过","北京","杨氏"});        int[] res=jdbcTemplate.batchUpdate(sql, batchArgs);        System.out.println(res.length);    }    @Test    public void testUpdate(){        String sql="update tb_user set name='稳杰',address='南海' where id=?";        int res=jdbcTemplate.update(sql,2);        System.out.println(res);    }    @Test    public void testDelete(){        String sql="delete from tb_user where id=?";        int res=jdbcTemplate.update(sql,12);        System.out.println(res);    }    //查询一个对象    @Test    public void testQueryOneObject(){        String sql="select id,name,address,last_name as lastName from tb_user where id=?";        User user=                jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), 22);        System.out.println(user);        System.out.println(user.getLastName());    }    //查询列表    @Test    public void testQueryList(){        String sql = "select * from tb_user where address like '%京%'";          List<User> userList=                (List<User>) jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));        System.out.println("查询List: ");        for (User user : userList) {            System.out.println(user);        }        System.out.println("数量: "+userList.size());    }    //查询列表    @Test    public void testQueryListWithArgs(){        String sql = "select * from tb_user where name=? and address = ?";          List<User> userList=                (List<User>) jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),new Object[]{"稳杰","北京"});        System.out.println("查询List: ");        for (User user : userList) {            System.out.println(user);        }        System.out.println("数量: "+userList.size());    }    //查询指定的字段    @Test    public void testMap(){        String param="京";        String sql = "select id,name,address from tb_user where address like '%"+param+"%' order by id desc ";        List<User> userList=                (List<User>) jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));        Map<String, Object[]> map = new HashMap<String, Object[]>();          for (User user:userList) {              Object[] userOtherMsg=new Object[2];            userOtherMsg[0]=user.getName();            userOtherMsg[1]=user.getAddress();            map.put(String.valueOf(user.getId()), userOtherMsg );          }          System.out.println("查询特定的字段: ");        for (Map.Entry<String, Object[]> entry:map.entrySet()) {            System.out.println(entry.getKey()+" -- " +entry.getValue()[0]+" -- "+entry.getValue()[1]);        }        System.out.println();    }    //查询数量    @Test    public void testQueryCount(){        String param="上海";        String sql="select Count(*) from tb_user where address like '%"+param+"%'";        @SuppressWarnings("deprecation")        long count=jdbcTemplate.queryForLong(sql);        System.out.println(count);    }    //查询数量    @Test    public void testQueryObject2(){        String arg="京";        String sql="select count(*) from tb_user where address like '%"+arg+"%'";        Long count=jdbcTemplate.queryForObject(sql, Long.class);        System.out.println(count);    }    @Test    public void testJDBC() throws SQLException{        DataSource dataSource=ctx.getBean(DataSource.class);        System.out.println(dataSource.getConnection());    }    @Test    public void testNamedParameterjdbcTemplate(){        String sql="insert into tb_user(name,address,last_name) values(:name,:addr,:ln)";        Map<String, Object> paramMap=new HashMap<>();        paramMap.put("name", "刘德华");        paramMap.put("addr", "香港");        paramMap.put("ln", "刘德华");        namedParameterJdbcTemplate.update(sql, paramMap);    }    @Test    public void testNamedParameterjdbcTemplate2(){        String sql="insert into tb_user(name,address,last_name) values(:name,:address,:lastName)";        User user=new User();        user.setName("钟林森");        user.setAddress("佛山");        user.setLastName("钟");        //面向对象的SQL        SqlParameterSource paramSource=new BeanPropertySqlParameterSource(user);        namedParameterJdbcTemplate.update(sql,paramSource);    }}

好了，就介绍这里吧！欢迎留言一同交流！

转载来源：http://blog.csdn.net/u013871100/article/details/51944988

阅读全文

0 0