JDBC
一、数据库连接池
1.数据库连接池
概念：存放数据库连接的一个容器（集合）Connection
当系统运行起来之后，这个连接池就被创建，在这个连接池当中，会申请一些对象，当有用户来访问数据库的时候，就从这个连接池当中获取连接对象，用户访问结束之后，连接池对象会归还给容器
2.连接池的实现
java官方提供了标准接口 DataSource(javax.sql包下)
//常用方法getConnection();//获取连接对象conn.close();//归还连接池对象
此接口由数据库厂商为我们实现
①C3P0 ②Druid阿里巴巴开发的数据库连接池
3.C3p0
导入jar包 2个定义配置文件 c3p0.properties或者c3p0-config.xml 配置文件直接放在src下创建数据源连接对象 DataSource获取连接public class demo1 {public static void main(String[] args) {try {//3. 创建数据源连接对象 DataSourceDataSource dataSource = new ComboPooledDataSource();//4. 获取连接Connection connection = dataSource.getConnection();System.out.println(connection.toString());} catch (SQLException throwables) {throwables.printStackTrace();}}}
案例
public class demo1 {public static void main(String[] args) {DataSource dataSource = null;Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {//3. 创建数据源连接对象 DataSourcedataSource = new ComboPooledDataSource();//4. 获取连接connection = dataSource.getConnection();String sql = "select * from mytest";preparedStatement = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();while (resultSet.next()){int id = resultSet.getInt("id");String name = resultSet.getString("sname");String pass = resultSet.getString("spwd");System.out.println(id+name+pass);}} catch (SQLException throwables) {throwables.printStackTrace();}finally {try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}try {preparedStatement.close();} catch (SQLException throwables) {throwables.printStackTrace();}try {resultSet.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}}
4.Druid
1.导入jar包
2.定义配置文件， 任意的名称和任意的位置 eg：xxx.properties
3.加载配置文件
4.创建数据源对象
5.获取连接
public class DruidDemo1 {public static void main(String[] args) {DataSource dataSource = null;Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {dataSource = new ComboPooledDataSource();connection = dataSource.getConnection();String sql = "select * from mytest";preparedStatement = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();while (resultSet.next()){int i = resultSet.getInt(1);String name = resultSet.getString("sname");String pass = resultSet.getString("spwd");System.out.println(i+"---"+name+"---"+pass);}} catch (SQLException throwables) {throwables.printStackTrace();}finally {try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}try {preparedStatement.close();} catch (SQLException throwables) {throwables.printStackTrace();}try {resultSet.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}}
工具类
创建实体类 user 和数据库表对应
定义一个工具类
提供静态代码块 加载配置文件 初始化连接池对象
提供方法
1.获取连接池对象的方法
2.释放资源的方法
3.获取数据源对象的方法
//工具类public class DruidUtiles {private static DataSource dataSource;static {//读取资源文件，获取值，执行一次Properties properties = new Properties();try {//加载配置文件properties.load(new FileReader("D:\\桌面\\JDBC\\JDBC-C3P0\\src\\druid.properties"));//初始化连接池对象dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}//1.获取连接池对象的方法public static Connection getConnection() throws SQLException {return dataSource.getConnection();}//2.释放资源的方法public static void close(Connection connection, PreparedStatement preparedStatement){if(connection!=null){try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if(preparedStatement!=null){try {preparedStatement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}//2.释放资源的方法public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){if(connection!=null){try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if(preparedStatement!=null){try {preparedStatement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if(resultSet!=null){try {resultSet.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}//3.获取数据源对象的方法public static DataSource getDataSource(){return dataSource;}}
测试
/*** 添加操作*/public class DruidDemo2 {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = DruidUtiles.getConnection();String sql="insert into mytest values(null,?,?)";preparedStatement = connection.prepareStatement(sql);preparedStatement.setString(1,"hhbh");preparedStatement.setString(2,"821821");int i = preparedStatement.executeUpdate();System.out.println(i);} catch (SQLException throwables) {throwables.printStackTrace();}finally {DruidUtiles.close(connection,preparedStatement);}}}//查询public class DruidDemo3 {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = DruidUtiles.getConnection();String sql="select * from mytest";preparedStatement = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();List<user> list = new ArrayList<>();while (resultSet.next()){user u = new user();u.setId(resultSet.getInt("id"));u.setName(resultSet.getString("sname"));u.setPassword(resultSet.getString("spwd"));System.out.println(u);}} catch (SQLException throwables) {throwables.printStackTrace();}finally {DruidUtiles.close(connection,preparedStatement,resultSet);}}}
二、JdbcTemPlate
Spring框架对JDBC的简单封装，提供了一个JdbcTemplate对象，大大的简化了开发
1.导入jar包 5个
2.创建一个JdbcTemPlate，依赖对象DataSource
3.调用JdbcTemPlate对象中的方法帮助我们实现增删改查
update();//执行增删改语句queryForMap();//查询结束，并将结果封装成Map集合queryForList();//查询结束，并将结果封装成Map集合,并将map存放在list当中query();//查询结果，并将结果封装成JavaBean对象queryForObject();//查询结果，并将结果封装成对象
练习：
修改 密码
添加
删除
查询所有记录 将结果封装成map------结果只有一条，列名为key，值为value
查询所有记录 将结果封装成list
查询所有记录 将结果封装成JavaBean
查询总记录数
public class jdbcTemplateDemo {private JdbcTemplate jt = new JdbcTemplate(DruidUtiles.getDataSource());//修改密码public void test1(){String sql = "update mytest set spwd = ? where id =?";int update = jt.update(sql, 730730, 8);System.out.println(update);}//添加public void test2(){String sql = "insert into mytest values(null,?,?)";int update = jt.update(sql,"张三", 77777);System.out.println(update);}//删除public void test3(){String sql = "delete from mytest where id = ?";int update = jt.update(sql,9);System.out.println(update);}//查询所有记录 将结果封装成mappublic void test4(){String sql = "select * from mytest where id = ?";Map<String, Object> map = jt.queryForMap(sql,1);System.out.println(map);}//查询所有记录 将结果封装成listpublic void test5(){String sql = "select * from mytest ";List<Map<String, Object>> maps = jt.queryForList(sql);System.out.println(maps);}//查询所有记录 将结果封装成JavaBean-----方法1public void test6(){String sql = "select * from mytest ";List<user> query = jt.query(sql, new RowMapper<user>() {@Overridepublic user mapRow(ResultSet resultSet, int i) throws SQLException {user u = new user();u.setId(resultSet.getInt("id"));u.setName(resultSet.getString("sname"));u.setPassword(resultSet.getString("spwd"));return u;}});System.out.println(query);}//查询所有记录 将结果封装成JavaBean-----方法2public void test7(){String sql = "select * from mytest ";List<user> query = jt.query(sql, new BeanPropertyRowMapper<user>(user.class));System.out.println(query);}//查询总记录数public void test8(){String sql = "select count (*) from mytest ";Long aLong = jt.queryForObject(sql,Long.class);System.out.println(aLong);}}
测试
public class test {public static void main(String[] args) {jdbcTemplateDemo jdbcTemplateDemo = new jdbcTemplateDemo();jdbcTemplateDemo.test1();jdbcTemplateDemo.test2();jdbcTemplateDemo.test3();jdbcTemplateDemo.test4();jdbcTemplateDemo.test5();jdbcTemplateDemo.test6();jdbcTemplateDemo.test7();jdbcTemplateDemo.test8();}}
