天天看點
傳回

瘋狂Java講義_Chapter13MySQL資料庫與JDBC程式設計：（1）JDBC概念與使用方法

1.JDBC的概念 

JDBC的全稱是Java Database Connectivity；​​
JDBC是Java連接配接資料庫的一組API，API與Java的接口部分是通用的，API與資料庫的接口部分根據資料庫的不同而不同，由廠家提供；
​​​​​​​JDBC模仿了ODBC，一種開放的資料庫連接配接；

2.JCBC常用的接口和類簡介

  • 參見page617

2.JDBC的連接配接方式

使用的jar包：mysql-connector-java-8.0.22

/*
2.JDBC程式設計步驟
    （1）加載資料驅動；
    （2）通過DriverManager擷取資料庫連接配接；
    （3）通過Connection對象建立Statement對象；
    （4）使用Statement執行SQL語句；
    （5）操作結果集；
 */

import java.sql.*;

public class ConnMySQL
{
    public static void main(String[] args) throws Exception
    {
        //加載驅動
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (
                //建立資料庫連接配接；
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://127.0.0.1:3306/select_test?useSSL=false&serverTimezone=UTC",
                        "root", "123456");
                //建立執行者；
                Statement stmt = conn.createStatement();

                //執行SQL，獲得結果；
                ResultSet rs = stmt.executeQuery("select s.*, teacher_name"
                        + " from student_table s, teacher_table t"
                        + " where t.teacher_id = s.java_teacher"))
        {
            //周遊結果
            while (rs.next())
            {
                System.out.println(rs.getInt(1) + "\t"
                        + rs.getString(2) + "\t"
                        + rs.getString(3) + "\t"
                        + rs.getString(4));
            }
        }
    }
}

3.使用executeUpdate()執行DDL和DML語句 

/*
1.使用executeUpdate()執行DDL和DML語句
    1.使用executeUpdate()執行DDL，傳回0；執行DML，傳回受影響的行數；
 */
import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDDL
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    //initParam用于初始化上述資訊；
    //Properties能夠根據配置資訊的名稱來加載配置資訊，進而簡化代碼量；
    public void initParam(String paramFile)
            throws Exception
    {

        var props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }


    public void createTable(String sql) throws Exception
    {

        Class.forName(driver);
        try (
                Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement())
        {
            stmt.executeUpdate(sql);
        }
    }


    public static void main(String[] args) throws Exception
    {
        var ed = new ExecuteDDL();
        ed.initParam("mysql.ini");
        //用executeUpdate執行DDL；
        ed.createTable("create table jdbc_test "
                + "( jdbc_id int auto_increment primary key, "
                + "jdbc_name varchar(255), "
                + "jdbc_desc text);");
        System.out.println("-----建表成功-----");
    }
}
           
import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDML
{
    private String driver;
    private String url;
    private String user;
    private String pass;

    public void initParam(String paramFile)
            throws Exception
    {

        var props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }


    public int insertData(String sql) throws Exception
    {

        Class.forName(driver);
        try (

                Connection conn = DriverManager.getConnection(url,
                        user, pass);

                Statement stmt = conn.createStatement())
        {

            return stmt.executeUpdate(sql);
        }
    }
    public static void main(String[] args) throws Exception
    {
        var ed = new ExecuteDML();
        ed.initParam("mysql.ini");
        int result = ed.insertData("insert into jdbc_test(jdbc_name, jdbc_desc)"
                + "select s.student_name, t.teacher_name "
                + "from student_table s, teacher_table t "
                + "where s.java_teacher = t.teacher_id;");
        System.out.println("系統中共有" + result + "條記錄受影響");
    }
}

4.使用execute()執行SQL語句 

/*
1.如果不知道要處理的SQL是什麼類型的（查詢或者插入），就用execute()方法，可以執行全部的SQL語句；
2.execute方法傳回是否傳回結果集的boolean值，擷取結果集，要用Statement對象的getResultSet()方法和getUpdateCount()方法；
 */

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteSQL
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {

        var props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }


    public void executeSql(String sql) throws Exception
    {

        Class.forName(driver);
        try (
                Connection conn = DriverManager.getConnection(url,
                        user, pass);
                Statement stmt = conn.createStatement())
        {
            //執行execute
            boolean hasResultSet = stmt.execute(sql);
            if (hasResultSet)   //結果集存在，執行的是查詢語句
            {
                try (
                        ResultSet rs = stmt.getResultSet())
                {
                    ResultSetMetaData rsmd = rs.getMetaData();  //ResultSetMetaData是用于分析結果集的中繼資料接口；
                    int columnCount = rsmd.getColumnCount();    //擷取行數

                    while (rs.next())
                    {
                        for (var i = 0; i < columnCount; i++)
                        {
                            System.out.print(rs.getString(i + 1) + "\t");
                        }
                        System.out.print("\n");
                    }
                }
            }
            else    //結果集不存在，執行的是插入或者删除語句
            {
                System.out.println("該SQL語句影響的記錄有"
                        + stmt.getUpdateCount() + "條");
            }
        }
    }


    public static void main(String[] args) throws Exception
    {
        var es = new ExecuteSQL();
        es.initParam("mysql.ini");
        System.out.println("------執行删除表的DDL語句-----");
        es.executeSql("drop table if exists my_test");
        System.out.println("------ִ執行建表的DDL語句-----");
        es.executeSql("create table my_test"
                + "(test_id int auto_increment primary key, "
                + "test_name varchar(255))");
        System.out.println("------ִ執行插入的DML語句-----");
        es.executeSql("insert into my_test(test_name) "
                + "select student_name from student_table");
        System.out.println("------ִ執行查詢的語句-----");
        es.executeSql("select * from my_test");
    }
}

5.使用PreparedStatement執行SQL語句 

/*
1.有時候執行的語句是類似的，隻是資料不同，為了能簡化代碼，可以采用占位符的形式：
    insert into student_table values(null, ?, ?);
    但是Statement執行不了，是以采用PreparedStatement可以執行這個語句；
2.使用PrepareStatement還可以防止SQL注入：
    
 */
import java.util.*;
import java.io.*;
import java.sql.*;

public class PreparedStatementTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {

        var props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
        Class.forName(driver);
    }

    public void insertUseStatement() throws Exception
    {
        long start = System.currentTimeMillis();    //記錄目前時間；
        try (
                Connection conn = DriverManager.getConnection(url,
                        user, pass);
                Statement stmt = conn.createStatement())
        {
            for (var i = 0; i < 100; i++)
            {
                stmt.executeUpdate("insert into student_table values("
                        + " null, '姓名" + i + "', 1)");
            }
            System.out.println("ʹ��Statement��ʱ:"
                    + (System.currentTimeMillis() - start));    //計算花費時間；
        }
    }

    public void insertUsePrepare() throws Exception
    {
        long start = System.currentTimeMillis();
        try (
                Connection conn = DriverManager.getConnection(url,
                        user, pass);
                PreparedStatement pstmt = conn.prepareStatement(
                        "insert into student_table values(null, ?, 1)"))
        {
            for (var i = 0; i < 100; i++)
            {
                pstmt.setString(1, "姓名" + i);   //設定SQL語句
                pstmt.executeUpdate();
            }
            System.out.println("使用PrepareStatement耗時:"
                    + (System.currentTimeMillis() - start));
        }
    }
    public static void main(String[] args) throws Exception
    {
        var pt = new PreparedStatementTest();
        pt.initParam("mysql.ini");
        pt.insertUseStatement();
        pt.insertUsePrepare();
    }
}

6.SQL注入及防範辦法

  • Sql注入是一種常見的Cracker式入侵方式，他利用SQL語句的漏洞來入侵 
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.*;
import java.io.*;
import java.sql.*;

public class LoginFrame
{
	private final String PROP_FILE = "mysql.ini";
	private String driver;
	
	private String url;
	private String user;
	private String pass;
	
	private JFrame jf = new JFrame("登入");
	private JTextField userField = new JTextField(20);
	private JTextField passField = new JTextField(20);
	private JButton loginButton = new JButton("登入");
	public void init() throws Exception
	{
		var connProp = new Properties();
		connProp.load(new FileInputStream(PROP_FILE));
		driver = connProp.getProperty("driver");
		url = connProp.getProperty("url");
		user = connProp.getProperty("user");
		pass = connProp.getProperty("pass");
		
		Class.forName(driver);
		
		loginButton.addActionListener(e -> {
			
			if (validate(userField.getText(), passField.getText()))
			{
				JOptionPane.showMessageDialog(jf, "登入成功");
			}
			
			else
			{
				JOptionPane.showMessageDialog(jf, "登入失敗");
			}
		});
		jf.add(userField, BorderLayout.NORTH);
		jf.add(passField);
		jf.add(loginButton, BorderLayout.SOUTH);
		jf.pack();
		jf.setVisible(true);
	}

//	private boolean validate(String userName, String userPass)
//	{
//		
//		var sql = "select * from jdbc_test "
//			+ "where jdbc_name = '" + userName
//			+ "' and jdbc_desc = '" + userPass + "'";
//		System.out.println(sql);
//		try (
//			Connection conn = DriverManager.getConnection(url, user, pass);
//			Statement stmt = conn.createStatement();
//			ResultSet rs = stmt.executeQuery(sql))
//		{
//           //假設在文本框輸入的語句為true，sql的結果集就會傳回true，造成誤判
//			if (rs.next())
//			{
//				return true;
//			}
//		}
//		catch (Exception e)
//		{
//			e.printStackTrace();
//		}
//		return false;
//	}


    //利用PreparedStatement可以避免SQL注入
	private boolean validate(String userName, String userPass)
	{
		try (
			Connection conn = DriverManager.getConnection(url, user, pass);
			PreparedStatement pstmt = conn.prepareStatement(
				"select * from jdbc_test where jdbc_name = ? and jdbc_desc = ?"))
		{
			pstmt.setString(1, userName);
			pstmt.setString(2, userPass);
			try (
				ResultSet rs = pstmt.executeQuery())
			{
				if (rs.next())
				{
					return true;
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}

	public static void main(String[] args) throws Exception
	{
		new LoginFrame().init();
	}
}
JavaBasic筆記
上一篇: Java-為什麼周遊輸出HashSet是有序的？*Java集合中有序無序的概念一、問題起因二、過程三、總結
下一篇: php檢測檔案内容,php判斷檔案是否為txt檔案

繼續閱讀