third----jdbc（ c3p0 || druid || SpringJDBCTemplete）

需要的jar：

 

 c3p0：

需要jar：

• mchange-commons-java-0.2.12.jar
• c3p0-0.9.5.2.jar

配置文件：c3p0-config.xml

 

<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
      <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/study?serverTimezone=Asia/Shanghai&amp;useUnicode=true&amp;characterEncoding=utf8</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">10</property>
    <property name="checkoutTimeout">3000</property>
  </default-config>
</c3p0-config>

 

测试连接：

package com.gton.c3p0Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @program: Jdbc-start
 * @description: C3P0
 * @author: GuoTong
 * @create: 2020-09-01 11:52
 **/
public class C3P0Test {
    public static void main(String[] args) {
        //创建数据库连接池
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //获取连接
        try {
            Connection connection = dataSource.getConnection();
            System.out.println(connection);
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Druid数据库连接池：

package com.gton.druidtest;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Properties;

/**
 * @program: Jdbc-start
 * @description: 德鲁伊数据库连接池，，阿里出品
 * @author: GuoTong
 * @create: 2020-09-01 14:12
 **/
public class DruidTest {
    public static void main(String[] args) throws Exception {

        //创建druid连接池
        Properties properties = new Properties();
        properties.load(DruidTest.class.getClassLoader().getResourceAsStream("druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //获取连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        //获取PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement("select * from student");
        ResultSet resultSet = preparedStatement.executeQuery();
        //处理
        //将数据封装成对象
        ArrayList<Student> list = new ArrayList<>();
        while (resultSet.next()){
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setAge(resultSet.getInt("age"));
            student.setScore(resultSet.getDouble("score"));
            student.setBirthday(resultSet.getDate("birthday"));
            //添加集合
            list.add(student);
        }
        //排序
        list.stream().sorted((o1,o2)->(int)(o2.getScore()-o1.getScore())).forEach(System.out::println);
       // Collections.sort();

        //释放资源(归还连接)
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

DruidUitl：

package com.gton.druidtest;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @program: Jdbc-start
 * @description: Druid工具类
 * @author: GuoTong
 * @create: 2020-09-01 15:23
 **/
public class DruidUtils {

    //初始化连接池
    static DataSource dataSource;

    static {
        //创建druid连接池
        try {
            Properties properties = new Properties();
            properties.load(DruidTest.class.getClassLoader().getResourceAsStream("druid.properties"));

            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource(){
        return dataSource;
    }
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

实体类：Javabean  ：

 

 dao设计模式：

package com.gton.dao;

import com.gton.JdbcUtils;
import com.gton.druidtest.DruidUtils;
import com.gton.druidtest.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: Jdbc-start
 * @description: 数据访问对象
 * @author: GuoTong
 * @create: 2020-09-01 15:18
 **/
public class StudentDao {
    //实体类和上层调用者之间的数据交换。
    //每一个方法，对应着对数据库的一系列操作。
    //通常为CRUD的基本操作

    //添加操作
    public void addStudent(Student student){
        Connection connection=null;
        PreparedStatement ps=null;
        //第一步获取连接，使用了数据库连接池Druid
        try {
            connection = DruidUtils.getConnection();
            String sql="insert into student values (null,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setInt(2,student.getAge());
            ps.setString(1,student.getName());
            ps.setDouble(3,student.getScore());
            ps.setDate(4, new java.sql.Date(student.getBirthday().getTime()));
            int i = ps.executeUpdate();
            if (i>=1){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.close(connection,ps);
        }

    }
    //查询全部账户
    public List<Student> getAllStudent(){
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        //第一步获取连接，使用了数据库连接池Druid
        try {
            connection = DruidUtils.getConnection();
            String sql="select * from student";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            ArrayList<Student> list = new ArrayList<>();
            while (resultSet.next()){
                Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                student.setScore(resultSet.getDouble("score"));
                student.setBirthday(resultSet.getDate("birthday"));
                //添加集合
                list.add(student);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.close(connection,ps,resultSet);
        }
        return null;
    }
    //根据id查询
    public List<Student> getById(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        //第一步获取连接，使用了数据库连接池Druid
        try {
            connection = DruidUtils.getConnection();
            String sql = "select * from student where id=?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            resultSet = ps.executeQuery();
            ArrayList<Student> list = new ArrayList<>();
            while (resultSet.next()) {
                Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                student.setScore(resultSet.getDouble("score"));
                student.setBirthday(resultSet.getDate("birthday"));
                //添加集合
                list.add(student);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection, ps, resultSet);
        }
        return null;
    }
    //根据id修改
    public void updateById(Student student){
        Connection connection=null;
        PreparedStatement ps=null;
        //第一步获取连接，使用了数据库连接池Druid
        try {
            connection = DruidUtils.getConnection();
            String sql="update student set name= ?,age=?,birthday=?,score=? where id=?";
            ps = connection.prepareStatement(sql);
            ps.setInt(2,student.getAge());
            ps.setString(1,student.getName());
            ps.setDouble(4,student.getScore());
            ps.setDate(3, new java.sql.Date(student.getBirthday().getTime()));
            ps.setInt(5,student.getId());
            int i = ps.executeUpdate();
            if (i>=1){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.close(connection,ps);
        }

    }

测试：

package com.gton.druidtest;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.gton.dao.StudentDao;
import org.junit.Assert;
import org.junit.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/**
 * @program: Jdbc-start
 * @description: 德鲁伊数据库连接池，，阿里出品
 * @author: GuoTong
 * @create: 2020-09-01 14:12
 **/
public class DruidTest2 {
    public static void main(String[] args) throws Exception {
        //添加
        StudentDao studentDao = new StudentDao();
       // studentDao.addStudent(new Student(1222,"雷蛇",23,56,new Date()));
        //查询全部
       // studentDao.getAllStudent().forEach(System.out::println);
        //根据id查询
       studentDao.getById(1007).forEach(System.out::println);
       //根据id修改
        studentDao.updateById(new Student(1007,"狂神",23,26,new Date()));

    }

    @Test
    public void addTest(){
        StudentDao studentDao = new StudentDao();
        List<Student> students = studentDao.getAllStudent();
        students.forEach(System.out::println);
        //使用断言判断
        //期望值，实际值比较。。。。
        Assert.assertEquals(4,students.size());
    }
}

SpringJDBCTemplete

package com.gton.springjdbc;

import com.gton.druidtest.DruidUtils;
import com.gton.druidtest.Student;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sound.midi.Soundbank;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @program: Jdbc-start
 * @description: SpringJdbc
 * @author: GuoTong
 * @create: 2020-09-01 16:43
 **/
public class SpringJdbcTest {

    private JdbcTemplate jdbcTemplate;

    public SpringJdbcTest() {
        this.jdbcTemplate = new JdbcTemplate(DruidUtils.getDataSource());
    }

    //添加
    public void add(Student student){
        int update = jdbcTemplate.update("insert into student values (null,?,?,?,?)",
                student.getName(),
                student.getAge(),
                student.getScore(),
                student.getBirthday());
        if (update>=0)
            System.out.println("添加成功");
        else
            System.out.println("添加失败");
    }
    //删除
    public void  deleteByid(int id){
        int update = jdbcTemplate.update("delete from student where id =?", id);
        if (update>=0)
            System.out.println("添加成功");
        else
            System.out.println("添加失败");
    }
    //查询全部
    public List<Student> findAll(){
        //自定义的封装，RowMapper  参数二传递封装的规则
       /* List<Student> query = jdbcTemplate.query("select * from student", new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                student.setScore(resultSet.getDouble("score"));
                student.setBirthday(resultSet.getDate("birthday"));
                return student;
            }
        });*/
       //自定义的封装简化  RowMapper---->   lambda
        List<Student> query = jdbcTemplate.query("select * from student", (resultSet, i) -> {
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setAge(resultSet.getInt("age"));
            student.setScore(resultSet.getDouble("score"));
            student.setBirthday(resultSet.getDate("birthday"));
            return student;
        });
        //如果实体类满足javabean，，，可以自动封装  BeanPropertyRowMapper
        //本质是反射--代理实现。。
        jdbcTemplate.query("select * from student",new BeanPropertyRowMapper<>(Student.class));

        return query;
    }

    //根据id查询
    public Long getById(int id){
        //queryForObject 该方法只能查询一个值，类似于，max，min的聚合查询
        Long student = jdbcTemplate.queryForObject("select count(*) from Student where id =?", new Object[]{1005},Long.class);
        //该方法只能查询一行数据
        Map<String, Object> map = jdbcTemplate.queryForMap("select * from Student where id =?", new Object[]{id});
        Set<String> set = map.keySet();
        for (String key : set) {
            System.out.println("key:"+key+", value:"+map.get(key));
        }

        //或者
        Set<Map.Entry<String, Object>> entries = map.entrySet();
        for (Map.Entry<String, Object> entry : entries) {
            System.out.println(entry.getKey()+":"+entry.getValue());
        }
        return student;

    }
}