package c3p0demo;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
public class c3p0demo {
/**
* c3p0 的演示
*/
public static void main(String[] args) throws SQLException {
ComboPooledDataSource ds = new ComboPooledDataSource();
Connection conn = ds.getConnection();
System.out.println(conn);
String sql="insert into bank values (?,?,?)";
PreparedStatement pr = conn.prepareStatement(sql);
pr.setInt(1,23601);
pr.setInt(2,231241);
pr.setInt(3,231);
int i = pr.executeUpdate();
System.out.println(i);
pr.close();
conn.close();
}
}
依赖如下
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/day25 root root
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
Druid
package Druiddemo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class demo2 {
public static void main(String[] args) throws SQLException {
Connection conn = demo1.connn();
String sql="insert into bank values (?,?,?)";
PreparedStatement pr = conn.prepareStatement(sql);
pr.setInt(1,224311);
pr.setInt(2,231141);
pr.setInt(3,2311);
int i = pr.executeUpdate();
//i大于0 成功
System.out.println(i);
demo1.close(conn,pr);
}
}
package Druiddemo;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.*;
import java.util.Properties;
public class demo1<conn> {
private static DataSource ds=null;
static {
try {
Properties pt=new Properties();
// pt.load(new FileReader("D:\\untitled2\\druid.properties"));
pt.load(demo1.class.getClassLoader().getResourceAsStream("druid.properties"));
ds=DruidDataSourceFactory.createDataSource(pt);
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource dss(){
return ds;
}
public static Connection connn() throws SQLException {
return ds.getConnection();
}
public static void close(Connection conn, Statement st){
close(conn, st, null);
}
public static void close(Connection conn, Statement st, ResultSet rs){
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Connection conn, PreparedStatement pr){
close(conn,pr,null);
}
public static void close(Connection conn, PreparedStatement pr, ResultSet rs){
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (pr!=null){
try {
pr.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
依赖
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///Demo
username=root
password=12345
initialSize=5
maxActive=10
maxWait=3000
Sping简单封装
依赖于数据源
使用druid的datasource对象
- Test6方法是根据demo1封装类封装
- Test7方法是根据demo3封装类封装
package spingjdbcdemo;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class demo2 {
private static JdbcTemplate jt=new JdbcTemplate(Druiddemo.demo1.dss());
/**
* 改
*/
public static void Test1(){
String sql="update bank set account=2211 where account=224311";
int update = jt.update(sql);
System.out.println("test1:"+update);
}
/**
*增加
*/
public static void Test2(){
String sql="Insert into bank (account,password) values (?,?)";
int update = jt.update(sql, 1, 3121);
System.out.println("test2:"+update);
}
public static void Test3(){
String sql="delete from bank where account = ?";
int update = jt.update(sql,1);
System.out.println("test3:"+update);
}
/**
* 返回一条数据
*/
public static void Test4(){
String sql="select * from bank where account=?";
Map<String, Object> stringObjectMap = jt.queryForMap(sql,2211);
System.out.println(stringObjectMap);
//结果:{account=2211, password=231141, balance=2311}
}
public static void Test5(){
String sql="select * from bank";
List<Map<String, Object>> maps = jt.queryForList(sql);
for (Map<String, Object> map : maps) {
System.out.println(map);
}
}
/**
* new RowMapper<封装类>()
* 重写 mapRow方法 用ResultSet类中的方法获取 用封装类封装 返回封装类对象
* jt.query(sql, new RowMapper<demo1>()方法返回list集合用iter快捷键遍历
*/
public static void Test6(){
String sql="select * from bank";
List<demo1> query = jt.query(sql, new RowMapper<demo1>() {
@Override
public demo1 mapRow(ResultSet resultSet, int i) throws SQLException {
int account = resultSet.getInt("account");
int password = resultSet.getInt("password");
int balance = resultSet.getInt("balance");
demo1 demo1 = new demo1();
demo1.setAccount(account);
demo1.setPassword(password);
demo1.setBalance(balance);
return demo1;
}
});
for (demo1 demo1 : query) {
System.out.println(demo1);
}
}
/**
* 类型为包装类型是因为 数据如果为空的时候 基本数据类型无法赋值就会报错
* 而包装类是可以为null的
* 泛型为定义封装的类名 和 封装类名的字节码文件
*/
public static void Test7(){
String sql="select * from bank";
List<demo3> query = jt.query(sql, new BeanPropertyRowMapper<demo3>(demo3.class));
for (demo3 s : query) {
System.out.println(s);
}
}
/**
* jt.queryForObject(sql,封装返回值结果的类型); 返回数据总量
*/
public static void Test8(){
String sql="select count(account) from bank";
Integer integer = jt.queryForObject(sql, Integer.class);
System.out.println(integer);
}
public static void main(String[] args) {
// Test1();
// Test2();
// Test3();
// Test4();
// Test5();
// Test6();
Test7();
Test8();
}
}
package spingjdbcdemo;
public class demo1 {
private int account;
private int password;
private int balance;
public String id;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public demo1(int account, int password, int balance) {
this.account = account;
this.password = password;
this.balance = balance;
}
public demo1() {
}
public int getAccount() {
return account;
}
public void setAccount(int account) {
this.account = account;
}
public int getPassword() {
return password;
}
public void setPassword(int password) {
this.password = password;
}
public int getBalance() {
return balance;
}
public void setBalance(int balance) {
this.balance = balance;
}
@Override
public String toString() {
return "demo1{" +
"account=" + account +
", password=" + password +
", balance=" + balance +
'}';
}
}
package spingjdbcdemo;
public class demo3 {
private Integer account=null;
private Integer balance=null;
private Integer password=null;
private String id;
public Integer getAccount() {
return account;
}
public void setAccount(Integer account) {
this.account = account;
}
public Integer getBalance() {
return balance;
}
public void setBalance(Integer balance) {
this.balance = balance;
}
public Integer getPassword() {
return password;
}
public void setPassword(Integer password) {
this.password = password;
}
@Override
public String toString() {
return "demo3{" +
"account=" + account +
", balance=" + balance +
", password=" + password +
'}';
}
}
需要导入的jar包