Spring之JDBC

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实体类

<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;}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", address=" + address+ ", lastName=" + lastName + "]";}}</span>

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

<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的配置文件：

<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.xsdhttp://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代码

<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");}/*测试增删改*/@Testpublic void testInsert(){String sql="insert into tb_user(name,address) values('李连杰','上海')";jdbcTemplate.execute(sql);}//批量更新@Testpublic 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);}@Testpublic void testUpdate(){String sql="update tb_user set name='稳杰',address='南海' where id=?";int res=jdbcTemplate.update(sql,2);System.out.println(res);}@Testpublic void testDelete(){String sql="delete from tb_user where id=?";int res=jdbcTemplate.update(sql,12);System.out.println(res);}//查询一个对象@Testpublic 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());}//查询列表@Testpublic 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());}//查询列表@Testpublic 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());}//查询指定的字段@Testpublic 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();        }//查询数量@Testpublic 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);}//查询数量@Testpublic 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);}@Testpublic void testJDBC() throws SQLException{DataSource dataSource=ctx.getBean(DataSource.class);System.out.println(dataSource.getConnection());}@Testpublic 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);}@Testpublic 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("钟");//面向对象的SQLSqlParameterSource paramSource=new BeanPropertySqlParameterSource(user);namedParameterJdbcTemplate.update(sql,paramSource);}}</span>

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