JdbcTemplate的配置及使用（c3p0)

 

1.c3p0配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
   <!--  默认配置  -->
   <default-config>
      <!--   连接参数  -->
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3308/sakila?serverTimezone=UTC</property>
      <property name="user">root</property>
      <property name="password">123456</property>
      <!--  连接池参数  -->
      <!-- 初始化申请的连接数量 -->
      <property name="initialPoolSize">5</property>
      <!-- 最大的连接数量 -->
      <property name="maxPoolSize">10</property>
      <!-- 超时时间 -->
      <property name="checkoutTimeout">3000</property>
   </default-config>
   <!--c3p0配置1-->
   <named-config name="c3p0">
      <!--   连接参数  -->
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3308/sakila?serverTimezone=UTC</property>
      <property name="user">root</property>
      <property name="password">2524151826</property>
      <!--  连接池参数  -->
      <property name="initialPoolSize">5</property>
      <property name="maxPoolSize">8</property>
      <property name="checkoutTimeout">1000</property>
   </named-config>
</c3p0-config>

2.jdbcUtils:

package utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtils {
    static ComboPooledDataSource cpds = new ComboPooledDataSource();

    public static Connection getConnection() throws SQLException{
        return cpds.getConnection();
    }

    //关闭操作
    public static void closeConnection(Connection con) throws SQLException{
        if(con!=null){
            con.close();
        }
    }
    public static void closeStatement(Statement st) throws SQLException{
        if(st!=null){
            st.close();
        }
    }
}

public class JDBCutils2 {
    public static DataSource source=new ComboPooledDataSource();

    public static DataSource getSource(){
        return source;
    }
}

3.BookDomain

package cn.edu.jdbctest;


public class BookDomain {
    private String id;
    private String bookname;
    private String writer;
    private int price;

    public BookDomain() {
    }

    public BookDomain(String id, String bookname, String writer, int price) {
        this.id = id;
        this.bookname = bookname;
        this.writer = writer;
        this.price = price;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getBookname() {
        return bookname;
    }

    public void setBookname(String bookname) {
        this.bookname = bookname;
    }

    public String getWriter() {
        return writer;
    }

    public void setWriter(String writer) {
        this.writer = writer;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "bookDomain{" +
                "id='" + id + '\'' +
                ", bookname='" + bookname + '\'' +
                ", writer='" + writer + '\'' +
                ", price=" + price +
                '}';
    }
}

4.Testdemo1:

package cn.edu.jdbctest;
 import org.junit.jupiter.api.BeforeAll;
 import org.junit.jupiter.api.Test;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.RowMapper;
 import utils.JDBCutils2;

 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.text.SimpleDateFormat;
 import java.util.Date;
 import java.util.List;
 import java.util.Map;

public class Demoq {
     static JdbcTemplate jdbcTemplate;

     @BeforeAll
     public static void init(){
         jdbcTemplate =new JdbcTemplate(JDBCutils2.getSource());
     }
     @Test
     public void add(){
         String sql="insert into book(id,bookname,writer,price)\n" +
                 "values('1002','三国演义','不知道','34')";
         jdbcTemplate.update(sql);
     }

     @Test
    public  void add1(){
         Date date=new Date();
         SimpleDateFormat sdf=new SimpleDateFormat("YYYY-MM-dd hh:mm:ss");
         System.out.println(sdf.format(date));
     }

     @Test
    public void selectForMap(){
         String sql="select * from book where id=?";
         Map<String,Object> map=null;

         map=jdbcTemplate.queryForMap(sql,"1000");
         System.out.println(map);
     }
     @Test
    public void queryListBean(){
         String sql="select * from book";
         List<cn.edu.jdbctest.BookDomain> list=null;
         list=jdbcTemplate.query(sql,new BookDomain());
         System.out.println(list);
     }



    public class BookDomain implements RowMapper<cn.edu.jdbctest.BookDomain> {


        @Override
        public cn.edu.jdbctest.BookDomain mapRow(ResultSet rs, int rowNum) throws SQLException {
            BookDomain bookDomain=new BookDomain();
            cn.edu.jdbctest.BookDomain bookDomain1=new cn.edu.jdbctest.BookDomain();
            bookDomain1.setId(rs.getString("id"));
            bookDomain1.setBookname(rs.getString("bookname"));
            return bookDomain1;
        }
    }


}