JDBC笔记3

最新推荐文章于 2024-10-24 23:13:54 发布

qq_35665476

最新推荐文章于 2024-10-24 23:13:54 发布

阅读量155

点赞数 1

文章标签： mysql jdbc java

本文链接：https://blog.csdn.net/qq_35665476/article/details/102709197

版权

PreparedStatement

优点：可以设置参数、可读性好、性能更好、可以防止sql注入式攻击

使用PreparedStatement

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestJDBC {
	public static void main(String[] args) {
		//从0开始查询2行
		list(0,2);
	}

	public static void list(int start, int count) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		String url="jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8";
		String user="root";
		String password="root";
		//?代表后续需要设置的参数
		//相较于statement的字符串拼接，这样的可读性明显更好
		String sql="insert into hero values(null,?,?,?)";
		
		try (
			Connection c = DriverManager.getConnection(url, user, password);
			//使用PreparedStatement
			PreparedStatement ps = c.prepareStatement(sql);
			) {
			//设置参数
			ps.setString(1, "提莫");
			ps.setFloat(2, 313.0f);
			ps.setInt(3, 50);
			//执行
			ps.execute();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

性能更好

可以用两种方式分别插入大量数据比较一下花费的时间。

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
  
public class TestJDBC {
    public static void main(String[] args) {
  
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
  
        String sql = "insert into hero values(null,?,?,?)";
        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8","root", "root");
             Statement s = c.createStatement();
             PreparedStatement ps = c.prepareStatement(sql);
            ) {
            // Statement执行10次，需要10次把SQL语句传输到数据库端
            // 数据库要对每一次来的SQL语句进行编译处理
        	long startTime1 = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                String sql0 = "insert into hero values(null," + "'提莫'" + ","
                        + 313.0f + "," + 50 + ")";
                s.execute(sql0);
            }
            s.close();
            long endTime1 = System.currentTimeMillis();
            System.out.println("Statement方式运行时间为："+(endTime1-startTime1));
  
            // PreparedStatement 执行10次，只需要1次把SQL语句传输到数据库端
            // 数据库对带?的SQL进行预编译
  
            // 每次执行，只需要传输参数到数据库端
            // 1. 网络传输量比Statement更小
            // 2. 数据库不需要再进行编译，响应更快
            long startTime2 = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                ps.setString(1, "提莫");
                ps.setFloat(2, 313.0f);
                ps.setInt(3, 50);
                ps.execute();
            }
            long endTime2 = System.currentTimeMillis();
            System.out.println("PreparedStatement方式运行时间为："+(endTime2-startTime2));
 
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
  
    }
}

防止sql注入式攻击

下面是sql注入攻击的一个例子，如果使用PreparedStatement可以避免这种问题。

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
  
public class TestJDBC {
    public static void main(String[] args) {
  
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8","root", "root");
             Statement s = c.createStatement();
        ) {
            //我们的本意只是查询英雄盖伦的信息，但如果使用下面这种方式（称之为sql注入），就会错误的导致所有英雄的信息都被检索出来
            String sql0 = "select * from hero where name = " + "'盖伦' OR 1=1";
            // 因为拼接出来的SQL语句是select * from hero where name = '盖伦' OR 1=1，因为有OR 1=1，所以式子恒成立
            // 那么就会把所有的英雄都查出来，而不只是盖伦
            // 如果Hero表里的数据是海量的，比如几百万条，把这个表里的数据全部查出来
            // 会让数据库负载变高，CPU100%，内存消耗光，响应变得极其缓慢
            ResultSet rs0 = s.executeQuery(sql0);
            int count=0;
            while (rs0.next()) {
                String heroName = rs0.getString("name");
                System.out.println(heroName);
                count++;
            }
            System.out.println("查询完成，总共"+count+"条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

execute与executeUpdate的区别

相同点：都可以执行增加，删除，修改

不同点：

execute可以执行查询语句，然后通过getResultSet，把结果集取出来，
executeUpdate不能执行查询语句；
execute返回boolean类型，true表示执行的是查询语句，false表示执行的是insert,delete,update等等，
executeUpdate返回的是int，表示有多少条数据受到了影响

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class TestJDBC {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8","root", "root");
            Statement s = c.createStatement();) {
            String sqlInsert = "insert into Hero values (null,'盖伦',616,100)";
            String sqlDelete = "delete from Hero where id = 100";
            String sqlUpdate = "update Hero set hp = 300 where id = 100";
            // 相同点：都可以执行增加，删除，修改
            s.execute(sqlInsert);
            s.execute(sqlDelete);
            s.execute(sqlUpdate);
            s.executeUpdate(sqlInsert);
            s.executeUpdate(sqlDelete);
            s.executeUpdate(sqlUpdate);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

JDBC特殊操作

获取数据库元数据

也就是获得和数据库服务器相关的数据，比如数据库版本，有哪些表，表有哪些字段，字段类型是什么等等。

package jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
  
public class TestJDBC {
    public static void main(String[] args) {
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	
    	String url="jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8";
    	String user="root";
    	String password="root";
    	try(
    		Connection c = DriverManager.getConnection(url, user, password);
    		) {
    		DatabaseMetaData dbmd = c.getMetaData();
    		// 获取数据库服务器产品名称
            System.out.println("数据库产品名称:\t"+dbmd.getDatabaseProductName());
            // 获取数据库服务器产品版本号
            System.out.println("数据库产品版本:\t"+dbmd.getDatabaseProductVersion());
            // 获取数据库服务器用作类别和表名之间的分隔符 如test.user
            System.out.println("数据库和表分隔符:\t"+dbmd.getCatalogSeparator());
            // 获取驱动版本
            System.out.println("驱动版本:\t"+dbmd.getDriverVersion());
            System.out.println("可用的数据库列表：");
            // 获取数据库名称
            ResultSet rs = dbmd.getCatalogs();
            while (rs.next()) {
                System.out.println("数据库名称:\t"+rs.getString(1));
            }
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}

获得自增长id

在Statement通过execute或者executeUpdate执行完插入语句后，MySQL会为新插入的数据分配一个自增长id，(前提是这个表的id设置为了自增长,在Mysql创建表的时候，AUTO_INCREMENT就表示自增长)，但是无论是execute还是executeUpdate都不会返回这个自增长id是多少。需要通过Statement的getGeneratedKeys获取该id。

package jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Statement;
  
public class TestJDBC {
    public static void main(String[] args) {
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	String url="jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8";
    	String user="root";
    	String password="root";
    	String sql = "insert into hero values(null,?,?,?)";
    	try(
    		Connection c = DriverManager.getConnection(url, user, password);
    		//注意区别
    		PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    		) {
    		ps.setString(1, "亚索");
    		ps.setFloat(2, 616);
            ps.setInt(3, 100);
            ps.execute();
            //getGeneratedKeys()获取该id
            ResultSet rs = ps.getGeneratedKeys();
            while(rs.next()) {
            	int id = rs.getInt(1);
                System.out.println(id);	
            }
    		
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}

JDBC事务操作

没有事务的前提下，假设一个操作是：加血，减血各做一次，所以结束后，英雄的血量不变
但是当减血的SQL不小心写错写成了updata(而非update)，那么最后结果是血量增加了，而非期望的不变。程序如下：

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {
    public static void main(String[] args) {
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	String url="jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8";
    	String user="root";
    	String password="root";
    	try(
    		Connection c = DriverManager.getConnection(url, user, password);
    		Statement s = c.createStatement();
    		) {
    		//加血的SQL
            String sql1 = "update hero set hp = hp +1 where id = 22";
            s.execute(sql1);
              
            //减血的SQL
            //不小心写错写成了 updata(而非update),这样执行这个语句报错就不会给它减去血量，
            String sql2 = "updata hero set hp = hp -1 where id = 22";
            s.execute(sql2);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}

所以使用事务处理上述操作，事务的本质是：事务内部的命令要么都成功，要么都失败，只有当内部的命令都成功的时候才会提交做出改变，否则不会提交作出改变。

通过 c.setAutoCommit(false);关闭自动提交
使用 c.commit();进行手动提交

程序如下：

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {
    public static void main(String[] args) {
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	String url="jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8";
    	String user="root";
    	String password="root";
    	try(
    		Connection c = DriverManager.getConnection(url, user, password);
    		Statement s = c.createStatement();
    		) {
    		//说明这条语句接下来的部分都属于一个事务
    		c.setAutoCommit(false);
            String sql1 = "update hero set hp = hp +1 where id = 22";
            s.execute(sql1);
            String sql2 = "updata hero set hp = hp -1 where id = 22";
            s.execute(sql2);
            //结束这个事务，提交
            c.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}

练习

设计一个代码，删除表中前10条数据，但是删除前会在控制台弹出一个提示：是否要删除数据(Y/N)，如果用户输入Y，则删除，如果输入N则不删除，如果输入的既不是Y也不是N，则重复提示。

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TestJDBC {
    public static void main(String[] args) {
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	String url="jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8";
    	String user="root";
    	String password="root";
    	
    	try(
    		Connection c = DriverManager.getConnection(url, user, password);
    		Statement s = c.createStatement();
    		Statement delete = c.createStatement();
    		) {
    		
    		//声明以下是一个事务
    		c.setAutoCommit(false);
    		String sql = "select * from hero limit 0,10";
    		ResultSet rs = s.executeQuery(sql);
    		while(rs.next()) {
    			int id = rs.getInt("id");
    			System.out.println("即将删除id="+id+"的数据");
    			//删除每一个检索出来的数据
        		String sql2 = "delete from hero where id="+id;
    			delete.execute(sql2);
    		}
    		
    		//键盘输入
    		Scanner scanner = new Scanner(System.in);
    		while(true) {//输入的既不是Y也不是N，无限循环的提示
    			System.out.println("请输入Y/N，从而确认/取消：");
        		String str = scanner.next();
        		if(str.equals("Y")) {//如果输入的是Y那么提交事务，执行删除
        			c.commit();
        			System.out.println("提交删除");
        			break;
        		}
        		if(str.equals("N")) {//输入的是N，不提交事务
        			System.out.println("放弃删除");
                    break;
        		}
    		}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}