天天看点
返回

疯狂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文件

继续阅读