jdbc数据库连接之jdbcUtils工具类抽取 -云博客

jdbc数据库连接之jdbcUtils工具类抽取

# 数据库 2024-05-05 14:29 0 29 来源：云博客

 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 block48  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 block67  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 block91  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 Administrator16  *17 */18 /**19  * @author Administrator20  *21 */22 /**23  * @author Administrator24  *25 */26 public class hello {27  @Test28 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 //执行sql40 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  @Test54 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;
           }
       }
   }
}