一、加载数据库驱动
注:加载数据库驱动时,首先要导入mysql-connector-java.jar,否则无法加载驱动。
下载地址: https://dev.mysql.com/downloads/connectors/j/5.1.html/
解压后就有mysql-connector-java.jar
导入idea
1.在intelij IDEA 中,点击File-Project Structure,出现界面的左侧点击Modules
在Dependencies标签界面下,点击+号新建“JARs or directories...”,到刚才下载的位置选中mysql-connector-java-5.1.44.jar,确定就OK了。导入其他jar也同样操作
三、创建Statement对象
1.Statement:用于执行不带参数的简单SQL语句
2.PreparedStatement:用于执行带或者不带参数的SQL语句
SQL语句会预编译在数据库系统
执行速度快于Statement对象
3.CallableStatement:用于执行数据库存储过程的调用
四、执行sql
import java.sql.*;
public class Main {
public static void main(String[] args) throws Exception {
String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost:3306/boot";
// 数据库的用户名与密码,需要根据自己的设置
final String USER = "root";
final String PASS = "12345678";
Connection conn = null;
Statement stmt = null;
try {
// 注册 JDBC 驱动
// 把Driver类装载进jvm
Class.forName("com.mysql.cj.jdbc.Driver");
// 打开链接
System.out.println("连接数据库...");
conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println(" 实例化Statement对...");
//创建statement对象
stmt = (Statement) conn.createStatement();
//查询
String sql = "SELECT * FROM tb_shengchanjihua";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
// 通过字段检索
int ID = rs.getInt("ID");
String Luci = rs.getString("Luci");
String JiHao = rs.getString("JiHao");
String GongYiLuJing=rs.getString("GongYiLuJing");
// 输出数据
System.out.print("ID: " + ID);
System.out.print(",炉次: " + Luci);
System.out.print(", 记号:" + JiHao);
System.out.print(",工艺路径: " + GongYiLuJing);
System.out.print("\n");
}
//更新update
String update_sql = "update tb_shengchanjihua set GongYiLuJing='EOFVOD1BOF' where ID=1";
stmt.execute(update_sql);
//增加insert
String inset_sql = "insert into tb_shengchanjihua (Luci,JiHao,GongYiLuJing) values(\"脱磷炉\", \"BCJD04\", \"BOFDEOVOD\")";
int i = stmt.executeUpdate(inset_sql);
//删除 delete
String delete_sql = "delete from tb_shengchanjihua";
//stmt.execute(delete_sql);
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
}
System.out.println("jdbc_test_end!");
}
}
传递参数的sql操作
public static void commn(String[]a){
String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost:3306/boot";
// 数据库的用户名与密码,需要根据自己的设置
final String USER = "root";
final String PASS = "12345678";
Connection conn = null;
Statement stmt = null;
try {
// 注册 JDBC 驱动
// 把Driver类装载进jvm
Class.forName("com.mysql.cj.jdbc.Driver");
// 打开链接
System.out.println("连接数据库...");
conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println(" 实例化Statement对...");
//创建statement对象
stmt = (Statement) conn.createStatement();
//查询
String sql = "SELECT * FROM tb_shengchanjihua";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
// 通过字段检索
int ID = rs.getInt("ID");
String Luci = rs.getString("Luci");
String JiHao = rs.getString("JiHao");
String GongYiLuJing=rs.getString("GongYiLuJing");
// 输出数据
System.out.print("ID: " + ID);
System.out.print(",炉次: " + Luci);
System.out.print(", 记号:" + JiHao);
System.out.print(",工艺路径: " + GongYiLuJing);
System.out.print("\n");
}
//更新update
String update_sql = "update tb_shengchanjihua set JiHuaHao=?,GongYiLuJing=? where ID=1";
PreparedStatement ptmt = conn.prepareStatement(update_sql); //预编译SQL,减少sql执行
//传参
ptmt.setString(1, a[1]);
ptmt.setString(2, a[2]);
ptmt.execute();
//增加insert
PreparedStatement ps = conn.prepareStatement("insert into tb_shengchanjihua (JiHao,JiHuaHao,GongYiLuJing) values(?,?,?)");
ps.setString(1, a[0]);
ps.setString(2, a[1]);
ps.setString(3, a[2]);
//执行
ps.execute();
//删除 delete
String delete_sql = "delete from tb_shengchanjihua where ID=?";
PreparedStatement ptmt1 = conn.prepareStatement(delete_sql);
//传参
ptmt1.setInt(1, 10);
//执行
ptmt1.execute();
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
}
System.out.println("jdbc_test_end!");
}