Spring JDBC

Spring框架对Spring的简单封装。提供一个JDBCTemplate对象简化JDBC开发

*步骤：

        1、导入jar包

        2、创建JDBCTemplate对象。依赖于数据源DataSource

                *JdbcTemplate template=new JdbcTemplate(ds);

        3、调用JdbcTemplate的方法来完成CRUD的操作

                *update()：执行DML语句。增删改语句

                *queryForMap():查询结果，将结果集封装为map集合

                *queryForList():查询结果，将结果集封装为list集合

                *query():查询结果，将结果封装为JavaBean对象

                *queryForobject：查询结果，将结果封装为对象

import com.itheima.utils.JDBCutils;
import org.springframework.jdbc.core.JdbcTemplate;public class JDBCTemplatedemo1 {public static void main(String[] args) {//1、导入jar包//2、创建JDBCTemplate对象JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCutils.dataSource());//3、调用方法String sql="update account set money=60000 where name=?";int count=jdbcTemplate.update(sql,"李四");System.out.println(count);}
}

import com.itheima.domain.Account;
import com.itheima.utils.JDBCutils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;import javax.activation.DataSource;
import java.util.List;
import java.util.Map;public class JDBCTemplatedemo2 {//获取jdbctemplet对象，private JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCutils.dataSource());//junit单元测试，可以让方法独立执行//修改张三的money为10000@Testpublic void test1(){//获取jdbctemplet对象，//2、定义sqlString sql="update account set money=80000 where name='张三'";//3、执行sqlint count=jdbcTemplate.update(sql);System.out.println(count);}@Testpublic void test2(){String sql="insert into account (name,money) values (?,?)";int count=jdbcTemplate.update(sql,"钱钱",90000);System.out.println(count);}
@Testpublic void test3(){
String sql="delete from account where name=?";
int count=jdbcTemplate.update(sql,"钱钱");System.out.println(count);}@Testpublic void test4(){String sql="select * from account where name=?";Map<String,Object> map= jdbcTemplate.queryForMap(sql,"张三");System.out.println(map);//{name=张三, money=80000}}@Testpublic void test5(){String sql="select * from account ";List<Map<String,Object>> list= jdbcTemplate.queryForList(sql);for (Map<String, Object> stringObjectMap : list) {System.out.println(stringObjectMap);/*** {name=张三, money=80000}* {name=李四, money=60000}* {name=王五, money=30000}* {name=王五, money=30000}*/}}//查询所有的记录，将其封装为account对象的list集合@Testpublic void test6(){String sql="select * from account ";List<Account>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Account>(Account.class));for (Account account : list) {System.out.println(account);}}/*** Account{name='张三', money=80000}* Account{name='李四', money=60000}* Account{name='王五', money=30000}* Account{name='王五', money=30000}*/}

public class Account {private String name;private int money;public String getName() {return name;}public void setName(String name) {this.name = name;}public int getMoney() {return money;}public void setMoney(int money) {this.money = money;}public Account(String name, int money) {this.name = name;this.money = money;}public Account() {}@Overridepublic String toString() {return "Account{" +"name='" + name + '\'' +", money=" + money +'}';}
}

import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Collections;
import java.util.Properties;/*** jdbc的连接池的工具类*/
public class JDBCutils {//1、定义成员变量private static   DataSource ds;static{try {//加载配置文件Properties properties=new Properties();properties.load(JDBCutils.class.getClassLoader().getResourceAsStream("Durid.properties"));//获取datasourcetry {ds= DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}} catch (IOException e) {e.printStackTrace();}}//获取连接的方法public static Connection conn() throws SQLException {return ds.getConnection();}public static void close(Statement statement,Connection connection){if (statement!=null){try {statement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (connection!=null){try {statement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}public static void close(ResultSet resultSet,Statement statement, Connection connection){if (resultSet!=null){try {resultSet.close();//归还连接} catch (SQLException throwables) {throwables.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (connection!=null){try {statement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}public static DataSource dataSource(){return ds;}
}