jdbc数据库连接之jdbcUtils工具类抽取
JDBC数据库连接
1.准备数据库
初始化数据库和表:
CREATE DATABASE day07;
USE day07;
create table category(
cid varchar(20) primary key,
cname varchar(20)
);
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');
db.properties
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=123
2.新建一个测试类
3.对jdbc进行抽取工具类
package com.demo.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ResourceBundle; public class jdbcUtils { public static final String DRIVERCLASS; public static final String URL; public static final String USERNAME; public static final String PASSWORD; static{ //加载配置文件 ResourceBundle bundle = ResourceBundle.getBundle("db"); DRIVERCLASS= bundle.getString("jdbc.driverClass"); URL = bundle.getString("jdbc.url"); USERNAME = bundle.getString("jdbc.username"); PASSWORD = bundle.getString("jdbc.password"); } //静态方法加载驱动 static{ try { Class.forName(DRIVERCLASS); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws Exception{ return DriverManager.getConnection(URL,USERNAME,PASSWORD); } //释放资源 public static void closeReasoure(Connection conn,Statement st,ResultSet rs ){ closeResultSet(rs); closeStatement(st); closeConn(conn); } /** * 释放连接 * @param conn 连接 */ public static void closeConn(Connection conn){ if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ conn=null; } } } /** * 释放语句执行者 * @param st 执行者 */ public static void closeStatement(Statement st){ if(st != null){ try { st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ st=null; } } } /* * * * @param rs * * */ /** * 释放结果集 * @param rs 结果集 */ public static void closeResultSet(ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ rs=null; } } } }
1 package com.demo.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 import java.util.ResourceBundle; 9 10 11 12 public class jdbcUtils { 13 14 //获取连接 15 public static Connection getConnection() throws Exception{ 16 17 ResourceBundle bundle = ResourceBundle.getBundle("db"); 18 String driverClass = bundle.getString("jdbc.driverClass"); 19 String url = bundle.getString("jdbc.url"); 20 String username = bundle.getString("jdbc.username"); 21 String password = bundle.getString("jdbc.password"); 22 23 Class.forName(driverClass); 24 Connection conn = DriverManager.getConnection(url,username,password); 25 return conn; 26 27 } 28 //释放资源 29 public static void closeReasoure(Connection conn,Statement st,ResultSet rs ){ 30 closeResultSet(rs); 31 closeStatement(st); 32 closeConn(conn); 33 34 35 } 36 37 38 /** 39 * 释放连接 40 * @param conn 连接 41 */ 42 public static void closeConn(Connection conn){ 43 if(conn != null){ 44 try { 45 conn.close(); 46 } catch (SQLException e) { 47 // TODO Auto-generated catch block 48 e.printStackTrace(); 49 }finally{ 50 conn=null; 51 } 52 } 53 } 54 55 56 57 /** 58 * 释放语句执行者 59 * @param st 执行者 60 */ 61 public static void closeStatement(Statement st){ 62 if(st != null){ 63 try { 64 st.close(); 65 } catch (SQLException e) { 66 // TODO Auto-generated catch block 67 e.printStackTrace(); 68 }finally{ 69 st=null; 70 } 71 } 72 } 73 74 /* 75 * 76 * 77 * @param rs 78 * 79 * */ 80 81 /** 82 * 释放结果集 83 * @param rs 结果集 84 */ 85 public static void closeResultSet(ResultSet rs){ 86 if(rs != null){ 87 try { 88 rs.close(); 89 } catch (SQLException e) { 90 // TODO Auto-generated catch block 91 e.printStackTrace(); 92 }finally{ 93 rs=null; 94 } 95 } 96 } 97 }
1 package com.demo.jdbc; 2 3 import static org.junit.Assert.*; 4 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.PreparedStatement; 8 import java.sql.ResultSet; 9 10 import org.junit.Test; 11 12 import com.mysql.jdbc.Driver; 13 14 /** 15 * @author Administrator 16 * 17 */ 18 /** 19 * @author Administrator 20 * 21 */ 22 /** 23 * @author Administrator 24 * 25 */ 26 public class hello { 27 @Test 28 public void testjdbc() throws Exception { 29 //注册驱动 30 Class.forName("com.mysql.jdbc.Driver"); 31 //获取连接对象 32 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123"); 33 //编写SQL语句 34 String sql = "select * from category"; 35 //创建执行者对象 36 PreparedStatement prepareStatement = con.prepareStatement(sql); 37 //设置参数 38 39 //执行sql 40 ResultSet rs = prepareStatement.executeQuery(); 41 //处理结果集 42 while(rs.next()){ 43 System.out.println(rs.getString("cid")+"::"+rs.getString("cname")); 44 } 45 //释放资源 46 rs.close(); 47 prepareStatement.close(); 48 con.close(); 49 50 } 51 52 //插入数据() 53 @Test 54 public void testjdbcUtils(){ 55 Connection conn=null; 56 ResultSet rs=null; 57 PreparedStatement st = null; 58 59 //获取连接 60 try { 61 conn= jdbcUtils.getConnection(); 62 63 String sql = "insert into category values(?,?)"; 64 65 st = conn.prepareStatement(sql); 66 67 st.setString(1,"c005"); 68 st.setString(2, "户外"); 69 70 int i=st.executeUpdate(); 71 72 if(i==1){ 73 System.out.println("success"); 74 }else{ 75 System.out.println("fail"); 76 } 77 78 } catch (Exception e) { 79 80 e.printStackTrace(); 81 82 }finally{ 83 //释放资源 84 jdbcUtils.closeReasoure(conn, st, rs); 85 } 86 } 87 }
package com.demo.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class jdbcUtils {
public static final String DRIVERCLASS;
public static final String URL;
public static final String USERNAME;
public static final String PASSWORD;
static{
ResourceBundle bundle = ResourceBundle.getBundle("db");
DRIVERCLASS= bundle.getString("jdbc.driverClass");
URL = bundle.getString("jdbc.url");
USERNAME = bundle.getString("jdbc.username");
PASSWORD = bundle.getString("jdbc.password");
}
//获取连接
public static Connection getConnection() throws Exception{
Class.forName(DRIVERCLASS);
Connection conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
return conn;
}
//释放资源
public static void closeReasoure(Connection conn,Statement st,ResultSet rs ){
closeResultSet(rs);
closeStatement(st);
closeConn(conn);
}
/**
* 释放连接
* @param conn 连接
*/
public static void closeConn(Connection conn){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
conn=null;
}
}
}
/**
* 释放语句执行者
* @param st 执行者
*/
public static void closeStatement(Statement st){
if(st != null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
st=null;
}
}
}
/*
*
*
* @param rs
*
* */
/**
* 释放结果集
* @param rs 结果集
*/
public static void closeResultSet(ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
rs=null;
}
}
}
}