JDBC快速入门
首先附上中文jdk文档
Java操作数据库步骤
0. 创建工程,导入jar包
导入jar包后不要忘记让IDEA识别jar包
1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
2. 获取链接
Connection conn = DriverManager.getConnection(url, username, password);
3. 定义SQL语句
String sql = "update...";
4. 获取执行SQL对象
Statement stmt = conn.createtatement();
5. 执行SQL
stmt.executeUpdate(sql);
6. 处理返回结果
7. 释放资源
以下为我的测试源码:
package test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception{
//1. 注册驱动,5.0版本后可以省略
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql = "update stu set money = 2000 where id = '1'";
//4. 获取执行sql对象的statement
Statement stmt = conn.createStatement();
//5. 执行sql
int count = stmt.executeUpdate(sql);
//6. 处理结果
System.out.println(count);
//7. 释放资源
stmt.close();
conn.close();
}
}
报错:
在后面自己发现因为时区问题会导致报错,因此修改源码:
package test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception{
//1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获取连接
String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql = "update stu set money = 2000 where id = '1'";
//4. 获取执行sql对象的statement
Statement stmt = conn.createStatement();
//5. 执行sql
int count = stmt.executeUpdate(sql);
//6. 处理结果
System.out.println(count);
//7. 释放资源
stmt.close();
conn.close();
}
}
运行结果:
API详解
Driver Manager(驱动管理类)作用:
1. 注册驱动
2. 获取数据库连接
Connection(数据库连接对象)作用:
1. 获取执行SQL对象
2. 管理事务
开启事务:setAutoCommit(boolean autoCommit): true为自动提交事务,false为手动提交事务即为开启事务
提交事务:commit()
回滚事务:rollback()
statement作用:
执行SQL语句
1. int executeUpdate(sql):执行DML,DDL语句
返回值:DML语句影响的行数;DDL语句执行后,执行成功也可能返回0
2. ResultSet executeQuery(sql):执行DQL语句
返回值:ResultSet结果集对象
- boolean next():
(1) 将光标从当前位置向前移动一行
(2) 判断当前行是否为有效行
- 返回值:true:有效行,当前行有数据;flase:无效行,当前行没有数据
- xxx getXxx(参数):获取数据
- xxx:数据类型;如:int getInt(),String getString()
- 参数:int->列的编号,从1开始;String->列的名称
package test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo_RestSet {
public static void main(String[] args) throws Exception{
// Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql:///jdbc?serverTimezone=UTC";
String usrname = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, usrname, password);
String sql = "select * from stu";
Statement stmt = conn.createStatement();
//执行sql
ResultSet rs = stmt.executeQuery(sql);
//处理结果,遍历rs中的数据,光标向下移动一行,并且判断当前是否有数据
while (rs.next())
{
//getXxx获取数据
String id = rs.getString(1);//String id = rs.getString("id");
int money = rs.getInt(2);// int money = rs.getInt("money");
String name = rs.getString(3);//String name = rs.getString("name");
System.out.println(id);
System.out.println(money);
System.out.println(name);
System.out.println("---------");
}
conn.close();
rs.close();
stmt.close();
}
}
执行结果:
PreparedStatement
- PreparedStatement作用:
1. 预编译SQL语句并执行语句:预防SQL注入问题(预编译开启要在url后加东西)
- 获取preparedstatement对象,将参数值用?替代
- 设置参数值,使用setXxx给?赋值
- 执行SQL,不需要再传递SQL
- SQL注入
SQL注入是通过操作输入来修改事先定义好的SQL语句,用以达到执行代码对服务器进行攻击的方法
package learning.jdbc;
import java.sql.*;
public class JDBCDemo_PreparedStatement {
public static void main(String[] args) throws Exception{
// Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql:///jdbc?serverTimezone=UTC&useServerPrepStmts=true";
String usrname = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, usrname, password);
String name = "root";
String pwd = "123";
//定义SQL
String sql = "select * from tb_user where username = ? and password = ?";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置?的值,会对敏感符号进行转义
pstmt.setString(1, name);
pstmt.setString(2, pwd);
//执行sql
ResultSet rs = pstmt.executeQuery();
//处理结果,遍历rs中的数据,光标向下移动一行,并且判断当前是否有数据
if (rs.next())
{
System.out.println("登陆成功");
}
else
{
System.out.println("登陆失败");
}
conn.close();
rs.close();
}
}