Java的JDBC操作
1、基本操作
// Main.java
package com.company;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
//1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接
String url = "jdbc:mysql://127.0.0.1:3306/myDB";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql = "update account 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();
}
}
2、对于数据库的增删改来说,提交均用stmt.executeupdate(sql)
,返回的是受影响的行数。对于查,比如select * from users
, 使用ResultSet rs = stmt.executequery(sql)
,返回的是符合条件的所有数据
// Main.java
package com.company;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
//1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接:如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
String url = "jdbc:mysql://127.0.0.1:3306/myDB";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql = "select * from account";
//4. 获取statement对象
Statement stmt = conn.createStatement();
//5. 执行sql
ResultSet rs = stmt.executeQuery(sql);
//6. 处理结果, 遍历rs中的所有数据
// 6.1 光标向下移动一行,并且判断当前行是否有数据
while (rs.next()){
//6.2 获取数据 getXxx()
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id);
System.out.println(name);
System.out.println(money);
System.out.println("--------------");
}
//7. 释放资源
rs.close();
stmt.close();
conn.close();
}
}
3、PreparedStatement
PreparedStatement作用:预编译SQL语句并执行:预防SQL注入问题
// 定义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();