JDBC快速入門-有解無憂

JDBC快速入門

詳解

１.０DriverManager

功能1

功能2

2.0 connection物件

3.0 statement物件

4.0 ResultSet

遍歷結果集的一個案例

import java.sql.*;

public class DQLtest {
    public static void main(String[] args)  {
        //注冊驅動
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet =null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //定義一個SQL陳述句
            String sql="select * from student";
            //資料庫連接物件 此處url要在原來資料庫后加上 "?serverTimezone=GMT%2B8&useSSL=false"
            //因為我匯入的jar包為最新版本 要添加ssl連接狀態
            connection = DriverManager.getConnection("jdbc:mysql:///db2" +
                    "?serverTimezone=GMT%2B8&useSSL=false", "root", "root");
            //執行SQL的物件
            statement = connection.createStatement();
            //獲取結果集物件
            resultSet = statement.executeQuery(sql);
            while(resultSet.next())//next方法判斷游標是否位于最后一行，是則回傳false 否則回傳true
            {
                int id = resultSet.getInt("ID");
                int age= resultSet.getInt("age");
                String name = resultSet.getString("name");
                String cls = resultSet.getString("class");
                System.out.println(id+" "+age+" "+name+" "+cls);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

列印結果：

1 18 lisa 191
2 19 JK 192
3 18 rose 191
4 19 V 192
5 20 suga 181
6 21 jimin 182

JDBC工具類的創建以及使用

1.0 組態檔

drive=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///db2?serverTimezone=GMT%2B8&useSSL=false
user=root
password=root

2.0 JDBCutil的創建

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCutil {
    /*
    * 1.靜態代碼塊中 注冊驅動、資料庫連接物件 url user password
    * 2.釋放資源
    * */
    public static String drive;
    public static String url;
    public static String user;
    public static String password;
    static {
        Properties properties = new Properties();
        //獲取src目錄下的檔案方式->類加載器 Classloader
        //先獲取位元組碼檔案
        ClassLoader classLoader = JDBCutil.class.getClassLoader();
        URL resource = classLoader.getResource("jdbc.properties");
        //URL 統一資源定位符 獲取絕對路徑
        String path = resource.getPath();
        try {
            properties.load(new FileReader(path));
        } catch (IOException e) {
            e.printStackTrace();
        }
        drive = properties.getProperty("drive");
         url = properties.getProperty("url");
         user = properties.getProperty("user");
         password = properties.getProperty("password");

    }
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url,user,password);
    }

    public static void close(Connection conn, Statement stat)
    {
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            stat.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //多載
    public static void close(Connection conn, Statement stat, ResultSet res)
    {
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            stat.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            res.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

對案例進行優化使用JDBCutil類

import java.sql.*;

public class DQLtest {
    public static void main(String[] args)  {
        //注冊驅動
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet =null;
        try{
            //Class.forName("com.mysql.cj.jdbc.Driver");
            //定義一個SQL陳述句
            String sql="select * from student";
 
           // connection = DriverManager.getConnection("jdbc:mysql:///db2" +
           //"?serverTimezone=GMT%2B8&useSSL=false", "root", "root");
           connection = JDBCutil.getConnection();

            //執行SQL的物件
           statement= connection.createStatement();
            //獲取結果集物件
           resultSet = statement.executeQuery(sql);
            while(resultSet.next())//next方法判斷游標是否位于最后一行，是則回傳false 否則回傳true
            {
                int id = resultSet.getInt("ID");
                int age= resultSet.getInt("age");
                String name = resultSet.getString("name");
                String cls = resultSet.getString("class");
                System.out.println(id+" "+age+" "+name+" "+cls);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCutil.close(connection,statement,resultSet);
        }
    }
}

頁面登錄案例

　PreparedStatement　

import java.sql.*;
import java.util.Scanner;

public class Usertest {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        System.out.println("請輸入您的姓名：");
        String username=input.next();
        System.out.println("請輸入您的密碼：");
        String password=input.next();
        Connection conn=null;
        PreparedStatement prep =null;
        ResultSet set=null;
        try{
            conn = JDBCutil.getConnection();
            //String sql="SELECT *FROM USER WHERE NAME='"+username+"'AND PASSWORD='"+password+"'";
            String sql="SELECT *FROM USER WHERE NAME=? AND PASSWORD=?";
            prep = conn.prepareStatement(sql);
            //給？賦值
            prep.setString(1,username);
            prep.setString(2,password);
            ResultSet resultSet = prep.executeQuery();
           if(resultSet.next())
           {
               System.out.println("登錄成功！");
           }
           else {
               System.out.println("登錄失敗！");
           }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCutil.close(conn,prep,set);
        }

    }
}

JDBC管理事務

資料庫連接池

1.0 介紹

重點介紹druid

下面就建立一個druid工具類來使用（mysql-connector-java-8.0.11.jar）（druid-1.1.21.jar）

定義組態檔druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///db2?serverTimezone=GMT%2B8&useSSL=false
username=root
password=root
#初始化連接數量
initialSize=5
#最大連接數
maxActive=10
#最大等待時間
maxWait=3000

定義一個工具類

package utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Druidutil {
    public static DataSource ds;
    //匯入jar包
    //定義組態檔
    //加載組態檔
    static{

        try {
            Properties prop = new Properties();
            prop.load(Druidutil.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //獲取連接物件
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //釋放資源

    public static void close(Statement stat,Connection conn)
    {
     close(null,stat,conn);
    }


    public static void close(ResultSet res,Statement stat, Connection conn)
    {
        if(res!=null)
        {
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(stat!=null)
        {
            try {
                stat.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null)
        {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //獲取連接池
    public static DataSource getDataSource()
    {
        return ds;
    }
}

定義一個測驗類來測驗一下這個工具類　

import utils.Druidutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Druidtest {
    public static void main(String[] args) {
        //需求：給db2中的表 student 添加一條資料
        Connection conn=null;
        PreparedStatement prep=null;
        try {
            //1.獲取連接
            conn = Druidutil.getConnection();
            //2.定義sql
            String sql="INSERT INTO student VALUES(NULL,?,?,?)";//使用prepareStatement
            //3.獲取prepareStatement物件
            prep = conn.prepareStatement(sql);
            prep.setInt(1,18);
            prep.setString(2,"Jennie");
            prep.setString(3,"191");
            //4.執行sql
            int result = prep.executeUpdate();
            //列印結果
            System.out.println(result);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            Druidutil.close(prep,conn);
        }

    }
}

（添加成功）

為了簡化JDBC的使用我們引入一個 Spring JDBC (JDBCTemplate)

1.DML陳述句

import org.springframework.jdbc.core.JdbcTemplate;
import utils.Druidutil;

public class SpringJDBC {
    private static JdbcTemplate template;
    public static void main(String[] args) {

        //執行DML陳述句
        //匯入jar包 獲取JDBCtemplate
        template = new JdbcTemplate(Druidutil.getDataSource());
        //test1();//修改操作
        //test2();//添加操作
        //test3();//洗掉操作
    }

    private static void test3() {
        String sql="delete from user where id=?";
        int update = template.update(sql, 3);
        System.out.println(update);
    }

    private static void test2() {
        String sql="insert into user (id,name,password) values (?,?,?)";
        int update = template.update(sql, 4, "bts", "613");
        System.out.println(update);
    }

    private static void test1()
    {
        //定義sql陳述句
        String sql="update user set password='121' where id=1";
        //執行sql
        int update = template.update(sql);
        System.out.println(update);
    }
}

2.DQL陳述句　　

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.Druidutil;

import java.util.List;
import java.util.Map;

public class SpringJDBC {
    private static JdbcTemplate template;

    public static void main(String[] args) {

        //執行DQL陳述句
        //匯入jar包 獲取JDBCtemplate
        template = new JdbcTemplate(Druidutil.getDataSource());
        //test1();//查詢一條記錄
        //test2();//查詢多條條記錄
        //test3();//將每條資料分裝為emp物件
         //test4();//查詢總記錄數
    }

    private static void test4() {
        String sql="select count(*) from user";
        Long aLong = template.queryForObject(sql, Long.class);
        System.out.println(aLong);
    }

    private static void test3() {
        String sql="select * from user";
        List<emp> query = template.query(sql, new BeanPropertyRowMapper<emp>(emp.class));
        for (emp emp : query) {
            System.out.println(emp);
        }
    }

    private static void test2() {
        String sql="select * from user";
        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }
    }

    private static void test1() {
        String sql="select * from user where id=?";
        Map<String, Object> map = template.queryForMap(sql, 3);
        System.out.println(map);
    }
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/6049.html

標籤：MySQL

上一篇：MYSQL實作將B表中的欄位值到更新A表中

下一篇：MySQL學習筆記（19）：優化資料庫物件

JDBC快速入門

JDBC快速入門

詳解

JDBC工具類的創建以及使用

頁面登錄案例

JDBC管理事務

資料庫連接池

為了簡化JDBC的使用 我們引入一個 Spring JDBC (JDBCTemplate)

為了簡化JDBC的使用我們引入一個 Spring JDBC (JDBCTemplate)