jdbc正确的使用方式

這裡寫目錄标題

• • 環境
  • 一.不用工具類操作資料庫
  • 二.用工具類操作資料庫
  • 三.用工具類的登入demo

環境

1.導入jar包

2.寫配置檔案并标記成類路徑

jdbc.properties

jdbc.properties:

jdbc.username=root
jdbc.password=root
jdbc.url=jdbc:mysql://localhost:3306/day20
jdbc.table="user"
           

一.不用工具類操作資料庫

demo1.java

public class demo1{
//查
@Test 
public void test{
//配置檔案
Properties properties = new Properties();
//當目前類加載時就獲得類路徑下配置檔案的流，前提是配置資源在配置類路徑下 mark as resources root
InputStream ip=jdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
    //properties 加載配置檔案
    properties.load(ip);
} catch (IOException e) {
    e.printStackTrace();
}
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
url = properties.getProperty("jdbc.url");
table=properties.getProperty("jdbc.table");

//1.注冊驅動
Class.forName("com.mysql.jdbc.Driver");
//2.獲得連接配接
Connection connection=DriverManager.getConnection(url,userName,passWord);
//3.建立預編譯sql對象
PreparedStatement pstm= connection.prepareStatement(sql);
//4.執行sql語句
String sql="select * from user ";
//傳回集
    ResultSet resultSet =pstm.executeQuery(sql);
    List<User> list=new ArrayList<>();
    //周遊類型于疊代器的傳回集
    while (resultSet.next())
    {
        User user  =new User();
       // User user = new User(resultSet.getInt("id"),//沒拿到值
        user.setId(resultSet.getInt("id"));//設定到了值
        user.setUsername(resultSet.getString("username"));
        user.setPassword(resultSet.getString("password"));
        user.setNickname(resultSet.getString("nickname"));
        list.add(user);
        /*System.out.print(resultSet.getObject(1)+"  ");
        System.out.print(resultSet.getObject(2)+"  ");
        System.out.print(resultSet.getObject(3)+"  ");
        System.out.println(resultSet.getObject(4));*/
        System.out.println("------------------------------------------------");

    }
    for (User user : list) {
        System.out.println(user);
    }

    //5.釋放資源
    if(resultSet!=null)
    {
        resultSet.close();
    }
    if(statement!=null){
        statement.close();
    }
    if(connection!=null){
        connection.close();
    }
}


//增
@Test
public void insert(){
//1.加載驅動
Class.forName("com.mysql.jdbc.Driver");
//配置檔案也隻用擷取一次
Properties properties = new Properties();
//當目前類加載時就獲得類路徑下配置檔案的流，前提是配置資源在配置類路徑下 mark as resources root
InputStream ip=jdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
    //properties 加載配置檔案
    properties.load(ip);
} catch (IOException e) {
    e.printStackTrace();
}
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
url = properties.getProperty("jdbc.url");
table=properties.getProperty("jdbc.table");
//擷取連接配接
 DriverManager.getConnection(url,username,password);
 //建立sql語句讓執行對象執行；
String sql="insert into user values(null,'zl',?,'趙六')";
//3.建立sql語句對象
PreparedStatement pstm = connection.prepareStatement(sql);
pstm.set(1,"123456");
int rows = pstm.executeUpdate(sql);
System.out.println("受影響行數"+rows);
//5.釋放資源
if(statement==null)
{
    statement.close();
}
if(connection==null){
    connection.close();
}
}
}
           

增删改類似，都是調用executeUpdate(sql)方法。

二.用工具類操作資料庫

因為每次調用方法的步驟都類似，建立驅動，傳回連接配接，執行sql。

頻繁的建立驅動，建立連接配接很消耗資源。

一個項目應該隻要一個驅動。

jdbc.utils工具類

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.time.Period;
import java.util.Properties;

/**
 * @Author:ZZZ
 * @Date: 2020/11/19 16:43
 * @Version 1.0
 */

//jdbc工具類
public class jdbcUtils {
    //靜态代碼塊裡加載配置檔案時用到，以及後面的連接配接資料庫用到
    private static String username;
    private static String password;
    private static String url;
    private static String table;

    //驅動隻用建立一次,是以在靜态代碼塊裡寫好
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver" );
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //配置檔案也隻用擷取一次
        Properties properties = new Properties();
        //當目前類加載時就獲得類路徑下配置檔案的流，前提是配置資源在配置類路徑下 mark as resources root
        InputStream ip=jdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            //properties 加載配置檔案
            properties.load(ip);
        } catch (IOException e) {
            e.printStackTrace();
        }
        username = properties.getProperty("jdbc.username");
        password = properties.getProperty("jdbc.password");
        url = properties.getProperty("jdbc.url");
        table=properties.getProperty("jdbc.table");
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url,username,password);
        //不再寫到preparedStatement是因為需要傳遞sql進來，和外面的執行一樣就沒必要寫了
        // PreparedStatement ps = connection.prepareStatement(sql);

        return connection;
    }
    
    //統一釋放資源  ,先加載的後釋放  ,查詢有結果集的多一個參數
    public static void close(ResultSet re,Statement stmt, Connection conn) throws SQLException {
        close(stmt,conn);
        if(re!=null)
        {
            re.close();
        }
    }
    //重載
    public static void close(Statement stmt, Connection conn) throws SQLException {
        if(stmt!=null)
        {
            stmt.close();
        }
        if(conn!=null)
        {
            conn.close();
        }

    }

   //結果
    public static void re(ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            int id1 = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            System.out.println("id: "+id1+" username: "+username+" password: "+password+" nickName: "+nickname);
        }
    }
}
           

三.用工具類的登入demo

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author:ZZZ
 * @Date: 2020/11/19 17:45
 * @Version 1.0
 */
            public class 登入demo {
                public static void main(String[] args) throws Exception{
                    Connection conn=jdbcUtils.getConnection();
                    String username="zzz";
                    String password="123456";
                    String sql="select * from user where username=? and password=?";
                    PreparedStatement pstm = conn.prepareStatement(sql);
                    pstm.setString(1,username);
                    pstm.setString(2,password);
                    ResultSet resultSet = pstm.executeQuery();
                    boolean next = resultSet.next();
                    if(next==true)
        {
            System.out.println("登入成功");
        }
        else
        {
            System.out.println("登入失敗");
        }
    }
}