绕来绕去还是来学Java了，跟着网课快速过了一遍java基础和数据库基础，用到啥再回去看吧，先直接上手JDBC。
JDBC编程 - 廖雪峰的官方网站 (liaoxuefeng.com)
教程是以mysql为例，我就直接用postgreSql了，反正只是驱动不一样。
新建java工程，在src里新建类，直接在主函数里写个sql执行方法。
首先要装个驱动Download | pgJDBC (postgresql.org)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbcDemo {
public static void main(String[] args) throws Exception{
//1.注册驱动
Class.forName("org.postgresql.Driver");
//2.获取链接
String url = "jdbc:postgresql://localhost:5432/Test0";
String username = "postgres";
String password = "***";
Connection conn = DriverManager.getConnection(url,username,password);
//3.定于SQL语句
String sql = "Insert into test values(3,'xiaowang');";
//4.获取执行sql的对象statement
Statement stmt = conn.createStatement();
//5.执行sql
int count = stmt.executeUpdate(sql);//返回受影响的行数
System.out.println(count);
stmt.close();
conn.close();
}
}
JDBC主要有以下几个API
1 DriverManager
注册驱动，获取connection连接对象，没啥说的
2 Connection
2.1执行SQL对象
普通执行SQL
预编译SQL，防止注入
执行存储过程的对象
2.2事务管理
public class JDBCDemo3_connection {
public static void main(String[] args) throws Exception{
//1.注册驱动
Class.forName("org.postgresql.Driver");
//2.获取链接
String url = "jdbc:postgresql://localhost:5432/Test0";
String username = "postgres";
String password = "·";
Connection conn = DriverManager.getConnection(url,username,password);
//3.定于SQL语句
String sql1 = "Insert into test values(3,'xiaowang');";
String sql2 = "Insert into test values(4,'xiaoming');";
//4.获取执行sql的对象statement
Statement stmt = conn.createStatement();
try {
// 开启事务
conn.setAutoCommit(false);
//5.执行sql
int count1 = stmt.executeUpdate(sql1);//返回受影响的行数
System.out.println(count1);
int count2 = stmt.executeUpdate(sql2);//返回受影响的行数
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception throwables) {
conn.rollback();
throwables.printStackTrace();
}
stmt.close();
conn.close();
}
}
//模板
Connection conn = openConnection();
try {
// 关闭自动提交:
conn.setAutoCommit(false);
// 执行多条SQL语句:
insert(); update(); delete();
// 提交事务:
conn.commit();
} catch (SQLException e) {
// 回滚事务:
conn.rollback();
} finally {
conn.setAutoCommit(true);
conn.close();
}3 Statement
3.1 int executeUpdate(sql); 执行DML，DDL语句
public class JDBCDemo4_Statement {
//执行DML语句
@Test
public void testDML() throws Exception{
//1.注册驱动
Class.forName("org.postgresql.Driver");
//2.获取链接
String url = "jdbc:postgresql://localhost:5432/Test0";
String username = "postgres";
String password = "Lxz325625";
Connection conn = DriverManager.getConnection(url,username,password);
//3.定于SQL语句
String sql = "update test set debt=50 where name='xiaowang'";
//4.获取执行sql的对象statement
Statement stmt = conn.createStatement();
//5.执行sql
int count = stmt.executeUpdate(sql);//执行完DML函数返回受影响的行数
//System.out.println(count);
if(count>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
stmt.close();
conn.close();
}
3.2 ResultSet executeQuery(sql); 执行DQL语句
4 ResultSet
@Test
public void testDML() throws Exception{
//1.注册驱动
Class.forName("org.postgresql.Driver");
//2.获取链接
String url = "jdbc:postgresql://localhost:5432/Test0";
String username = "postgres";
String password = "Lxz325625";
Connection conn = DriverManager.getConnection(url,username,password);
//3.定于SQL语句
String sql = "Select * from test";
//4.获取执行sql的对象statement
Statement stmt = conn.createStatement();
//5.执行sql
try(ResultSet rs = stmt.executeQuery(sql)){
while(rs.next()){
int debt = rs.getInt(1);//列号或者列名
String name = rs.getString(2);
System.out.println(debt);
System.out.println(name);
}
}
stmt.close();
conn.close();
}ResultSet也是需要关闭的资源，用try可以执行完关闭或使用close函数关闭
EX：PreparedStatement
避免SQL注入，使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
ps.setObject(1, "M"); // 注意：索引从1开始
ps.setObject(2, 3);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
long id = rs.getLong("id");
long grade = rs.getLong("grade");
String name = rs.getString("name");
String gender = rs.getString("gender");
}
}
}
}Update数据同理
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO students (id, grade, name, gender) VALUES (?,?,?,?)")) {
ps.setObject(1, 999); // 注意：索引从1开始
ps.setObject(2, 1); // grade
ps.setObject(3, "Bob"); // name
ps.setObject(4, "M"); // gender
int n = ps.executeUpdate(); // 1
}
}插入数据并获得主键
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO students (grade, name, gender) VALUES (?,?,?)",
Statement.RETURN_GENERATED_KEYS)) {
ps.setObject(1, 1); // grade
ps.setObject(2, "Bob"); // name
ps.setObject(3, "M"); // gender
int n = ps.executeUpdate(); // 1
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
long id = rs.getLong(1); // 注意：索引从1开始
}
}
}
}5 Batch
提升批量写入速度
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
// 对同一个PreparedStatement反复设置参数并调用addBatch():
for (Student s : students) {
ps.setString(1, s.name);
ps.setBoolean(2, s.gender);
ps.setInt(3, s.grade);
ps.setInt(4, s.score);
ps.addBatch(); // 添加到batch
}
// 执行batch:
int[] ns = ps.executeBatch();
for (int n : ns) {
System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
}
}6 连接池
Comments NOTHING