- JDBC学习笔记01
- 获取数据库连接的5种方式
- 方式1
- 方式2
- 方式3
- 方式4(推荐使用)
- 方式5
- 细节
- ResultSet(结果集)
- 基本介绍
- Statement
- 基本介绍
- PreparedStatement
- 基本介绍
- 预处理好处
- JBDC API 小结
- 事务
- 基本介绍
- 应用实例
- 批处理
- 基本介绍
- 应用实例
Driver driver = new Driver();
//2.得到连接
//协议jdbc:mysql://通过jdbc的方式连接mysql
//localhost 主机,也可以是ip地址
//连接到哪个数据库
String url = "jdbc:mysql://localhost:3306/db02";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123456");
//可以把connect当成网络连接
Connection connect = driver.connect(url, properties);
方式2
//使用反射加载Driver类,动态加载,更加的灵活,减少依赖性
Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/db02";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "123456");
Connection connect = driver.connect(url, properties);
System.out.println(connect);
方式3
//使用反射加载Driver类,扩展性好
Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
//创建url
String url = "jdbc:mysql://localhost:3306/db02";
String user = "root";
String password = "123456";
DriverManager.registerDriver(driver);//注册驱动
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
方式4(推荐使用)
//使用Class.forName 自动完成注册驱动,简化代码
//在加载Driver类时,完成注册
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db02";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
底层静态代码块在类加载的时候做了注册
mysql.properties
user=root
password=123456
url=jdbc:mysql://localhost:3306/db02
driver=com.mysql.cj.jdbc.Driver
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
细节
-
mysqL驱动5.1.6可以无需CLass.forName(“com.mysql.jdbc.Driver”);
-
从jdk1.5以后使用了jdbc4,不再需要显示调用class.forName()注册驱动而是自动调用驱动jar包下META-INF\servicesVjava .sqI.Driver文本中的类名称去注册
3. 建议写上
-
表示数据库结果集的数据表,通常通过执行查询数据库的语句生成
-
ResultSet对象保持一个光标指向其当前的数据行。最初,光标位于第一行之前next方法将光标移动到下一行,并且由于在ResultSet对象中没有更多行时返回false,因此可以在while循环中使用循环来遍历结果集
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select id,name,sex,borndate from actor";
//执行给定的sql语句,返回单个ResultSet对象
ResultSet resultSet = statement.executeQuery(sql);
//循环取出
while (resultSet.next()) {//让光标向后移动,如果没有更多行,返回false
int id = resultSet.getInt(1);//获取该行的第一列
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
}
resultSet.close();
statement.close();
connection.close();
Statement
基本介绍
-
Statement对象用于执行静态SQL语句并返回其生成的结果的对象
-
在连接建立后,需要对数据库进行访问,执行命名或是SQL语句,可以通过
Statement [存在SQL注入]
PreparedStatement[预处理]
CallableStatement[存储过程] -
Statement对象执行SQL语句,存在SQL注入风险
-
SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的SQL语句段或命令,恶意攻击数据库。
-
要防范SQL注入,只要用PreparedStatement(从Statement扩展而来)取代Statement就可以了
String name = "";
String pwd = "";
Scanner scanner = new Scanner(System.in);
name = scanner.nextLine();
pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select name, pwd from admin where name = '" + name + "' and pwd = '" + pwd + "'";
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("登陆成功");
} else {
System.out.println("登陆失败");
}
resultSet.close();
statement.close();
connection.close();
PreparedStatement
基本介绍
- PreparedStatement 执行的SQL语句中的参数用问号(?)来表示,调用PreparedStatement对象的setXxx()方法来设置这些参数。setXxx()方法有两个参数,第一个参数是要设置的SQL语句中的参数的索引(从1开始),第二个是设置的SQL语句中的参数的值
- 调用executeQuery0,返回ResultSet 对象
- 调用executeUpdate:执行更新,包括增、删、修改
- 不再使用+拼接sql语句,减少语法错误
- 有效的解决了sql注入问题!
- 大大减少了编译次数,效率较高
执行select用executeQuery()
执行dml(update,insert,delete) 使用 executeUpdate()
String name = "";
String pwd = "";
Scanner scanner = new Scanner(System.in);
name = scanner.nextLine();
pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "select name, pwd from admin where name = ? and pwd = ?";
//preparedStatement 对象实现了PreparedStatement接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,name);
preparedStatement.setString(2,pwd);
//执行select用executeQuery()
//执行dml(update,insert,delete) 使用 executeUpdate()
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println("成功");
} else {
System.out.println("失败");
}
resultSet.close();
preparedStatement.close();
connection.close();
String name = "";
String pwd = "";
Scanner scanner = new Scanner(System.in);
name = scanner.nextLine();
pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
//String sql = "insert into admin values(?,?)";
String sql = "update admin set pwd = ? where name = ?";
//String sql = "delete from admin where name = ?";
//preparedStatement 对象实现了PreparedStatement接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,pwd);
preparedStatement.setString(2,name);
//执行select用executeQuery()
//执行dml(update,insert,delete) 使用 executeUpdate()
int rows = preparedStatement.executeUpdate();
if (rows > 0) {
System.out.println("成功");
} else {
System.out.println("失败");
}
preparedStatement.close();
connection.close();
JBDC API 小结
事务
基本介绍
- JDBC程序中当一个Connection对象创建时,默认情况下是自动提交事务:每次执行一个SQL语句时,如果执行成功,就会向数据库自动提交,而不能回滚。
- JDBC程序中为了让多个SQL语句作为一个整体执行,需要使用事务
- 调用Connection的setAutoCommit(false)可以取消自动提交事务
- 在所有的SQL语句都成功执行后,调用Connection.commit();方法提交事务
- 在其中某个 *** 作失败或出现异常时,调用rollback();方法回滚事务
转账业务
@Test
public void noTransaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "update account set balance = balance - 100 where id = ?";
String sql2 = "update account set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();//默认情况connection自动提交
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
preparedStatement.executeUpdate();
int i = 1 / 0;//抛出异常,下面不会执行
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
//事务解决
@Test
public void useTransaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "update account set balance = balance - 100 where id = ?";
String sql2 = "update account set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();//默认情况connection自动提交
connection.setAutoCommit(false);//相当于开启了事务
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
preparedStatement.executeUpdate();
//int i = 1 / 0;//抛出异常,下面不会执行
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//提交事务
connection.commit();
} catch (Exception e) {
//这里可以回滚,默认回滚到事务的开始状态
System.out.println("发生异常,回滚");
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
批处理
基本介绍
-
当需要成批插入或者更新记录时。可以采用Java的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。
-
JDBC的批量处理语句包括下面方法:
addBatch():添加需要批量处理的SQL语句或参数
executeBatch():执行批量处理语句;
clearBatch():清空批处理包的语句 -
JDBC连接MySQL时,如果要使用批处理功能,请再url中加参数?rewriteBatchedStatements=true
-
批处理往往和PreparedStatement一起搭配使用,可以既减少编译次数,又减少运行次数,效率大大提高
@Test
public void noTransaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "update account set balance = balance - 100 where id = ?";
String sql2 = "update account set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();//默认情况connection自动提交
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
preparedStatement.executeUpdate();
int i = 1 / 0;//抛出异常,下面不会执行
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
//事务解决
@Test
public void useTransaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "update account set balance = balance - 100 where id = ?";
String sql2 = "update account set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();//默认情况connection自动提交
connection.setAutoCommit(false);//相当于开启了事务
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
preparedStatement.executeUpdate();
//int i = 1 / 0;//抛出异常,下面不会执行
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//提交事务
connection.commit();
} catch (Exception e) {
//这里可以回滚,默认回滚到事务的开始状态
System.out.println("发生异常,回滚");
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)