2021-05-14

封装mysql数据库连接

package com.util;

import javax.swing.plaf.nimbus.State;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
   static {
       try {
           //加载db.properties文件
           InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
           //创建properties对象
           Properties properties = new Properties();
           properties.load(in);
           driver = properties.getProperty("driver");
           url = properties.getProperty("url");
           username = properties.getProperty("username");
           password = properties.getProperty("password");
           //加载驱动
           Class.forName(driver);
       } catch (IOException | ClassNotFoundException e) {
           e.printStackTrace();
       }
   }

   //创建连接
    public static Connection getConnection() throws SQLException {
     return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    public static void release(Connection conn,Statement st,ResultSet rs) throws SQLException {
       if (rs != null){
           rs.close();
       }
        if (st != null){
            st.close();
        }
        if (conn != null){
            conn.close();
        }
    }
}

测试封装

import com.util.JdbcUtils;

import java.sql.*;

public class test {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select * from blog";
            ResultSet i = st.executeQuery(sql);
            while (i.next()){
                System.out.println(i.getObject("id"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

防止sql注入
package com;

import com.util.JdbcUtils;
import org.junit.Test;

import java.sql.*;

public class update {

    @Test
    public void test() throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "insert into student(id,name,age,uid) values(8,'z',45,9)";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }


    @Test
    public void test1() throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into student(id,name,age,uid) values(?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,8);
            st.setString(2,"永强");
            st.setInt(3,3);
            st.setInt(4,10);
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}