Java操纵数据库 —— JDBC ① 底子篇 - ToB企服应用市场:ToB评测及商务社交产业平台

package JavaJDBCBase;
import java.sql.*;
public class Demo1JDBCQuick {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取数据库连接
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
// 3.获取执行sql的对象，statement(把sql语句发送给MySQL)
Statement stmt = conn.createStatement();
// 4.编写sql语句并执行，以及接收返回的结果集
String sql = "select emp_id,emp_name,emp_salary,emp_age from t_tmp";
ResultSet result = stmt.executeQuery(sql);
// 5.处理结果，遍历result结果集 next方法，判断有没有下一行
while (result.next()) {
int empId = result.getInt("emp_id");
String empName = result.getString("emp_name");
double empSalary = result.getDouble("emp_salary");
int empAge = result.getInt("emp_age");
System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
}
// 6.释放资源先开启后关闭原则
result.close();
stmt.close();
conn.close();
}
}

复制代码

Class.forName("com.mysql.cj.jdbc.Driver");

复制代码

// 1. 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
DriverManager.registerDriver(new Driver());

复制代码

import java.sql.*;
import java.util.Scanner;
public class Demo2PreparedStatement {
public static void main(String[] args) throws Exception {
// 1.注册驱动
// 2.获取链接对象
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection conn = DriverManager.getConnection(url,username,password);
// 3.获取执行sql语句对象
PreparedStatement preparedStatement = conn.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_id=?");
System.out.println("请输入员工编号：");
Scanner sc = new Scanner(System.in);
String name = sc.nextLine();
// 4.为？占位符赋值，并执行sql语句，并执行，接受返回的结果
preparedStatement.setString(1,name);
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果，遍历esultSet
while(resultSet.next()){
int emp_id = resultSet.getInt("emp_id");
String emp_name = resultSet.getString("emp_name");
int emp_age = resultSet.getInt("emp_age");
double emp_salary = resultSet.getDouble("emp_salary");
System.out.println(emp_id+"\t"+emp_name+"\t"+emp_age+"\t"+emp_salary);
}
// 6.释放资源
resultSet.close();
preparedStatement.close();
conn.close();
}
}

复制代码

package JavaJDBCBase;
import java.sql.*;
import java.util.Scanner;
public class Demo2PreparedStatement {
public static void main(String[] args) throws Exception {
// 1.注册驱动
// 2.获取链接对象
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection conn = DriverManager.getConnection(url,username,password);
// 3.获取执行sql语句对象
PreparedStatement preparedStatement = conn.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_name=? || emp_salary=?");
System.out.println("请输入员工姓名：");
Scanner sc = new Scanner(System.in);
String name = sc.nextLine();
System.out.println("请输入员工工资");
double salary = sc.nextDouble();
// 4.为？占位符赋值，并执行sql语句，并执行，接受返回的结果 int类型：参数的下标，从0开始，要替换的值为多少
preparedStatement.setString(1,name);
preparedStatement.setDouble(2,salary);
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果，遍历resultSet
while(resultSet.next()){
int emp_id = resultSet.getInt("emp_id");
String emp_name = resultSet.getString("emp_name");
int emp_age = resultSet.getInt("emp_age");
double emp_salary = resultSet.getDouble("emp_salary");
System.out.println(emp_id+"\t"+emp_name+"\t"+emp_age+"\t"+emp_salary);
String goal = resultSet.getString("emp_id");
System.out.println(emp_name+"id为："+goal);
}
// 6.释放资源
resultSet.close();
preparedStatement.close();
conn.close();
}
}

复制代码

String goal = resultSet.getString("emp_name");
System.out.println(goal);

复制代码

package JavaJDBCBase;
import java.sql.*;
import java.util.Scanner;
public class Demo2PreparedStatement {
public static void main(String[] args) throws Exception {
// 1.注册驱动
// 2.获取链接对象
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection conn = DriverManager.getConnection(url,username,password);
// 3.获取执行sql语句对象
PreparedStatement preparedStatement = conn.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_id=?");
System.out.println("请输入员工编号：");
Scanner sc = new Scanner(System.in);
String name = sc.nextLine();
// 4.为？占位符赋值，并执行sql语句，并执行，接受返回的结果
preparedStatement.setString(1,name);
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果，遍历esultSet
while(resultSet.next()){
int emp_id = resultSet.getInt("emp_id");
String emp_name = resultSet.getString("emp_name");
int emp_age = resultSet.getInt("emp_age");
double emp_salary = resultSet.getDouble("emp_salary");
System.out.println(emp_id+"\t"+emp_name+"\t"+emp_age+"\t"+emp_salary);
String goal = resultSet.getString("emp_name");
System.out.println(goal);
}
// 6.释放资源
resultSet.close();
preparedStatement.close();
conn.close();
}
}

复制代码

// 单行单列查询
@Test
public void testQuerySingleRowAndCol() throws SQLException {
// 1.注册驱动
// 2.获取连接
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection connection = DriverManager.getConnection(url, username, password);
// 3.预编译SQL语句得到PreparedStatement对象
PreparedStatement preparedStatement = connection.prepareStatement("select count(*) as count from t_tmp");
// 4.执行语句获取结果
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果，进行遍历,如果明确只有一个结果，那么resultSet至少要做一次next的判断，才能拿到我们要的列的结果
while (resultSet.next()) {
// 用下标获取
int anInt = resultSet.getInt(1);
System.out.println(anInt);
}
// 6.释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}

复制代码

// 单行多列查询
@Test
public void testQuerySingleRowAndCol2() throws SQLException {
// 1.注册驱动
// 2.获取链接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBC", "root", "954926928lcl");
// 3.预编译SQL语句获得PreparedStatement对象
PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_id = ?");
// 4.为占位符赋值，然后执行，并接受结果
preparedStatement.setInt(1,5);
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果
while (resultSet.next()) {
int empId = resultSet.getInt("emp_id");
String empName = resultSet.getString("emp_name");
double empSalary = resultSet.getDouble("emp_salary");
int empAge = resultSet.getInt("emp_age");
System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
}
// 6.资源释放
resultSet.close();
preparedStatement.close();
connection.close();
}

复制代码

// 多行多列查询
@Test
public void testQueryMoreRow() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement preparedStatement = conn.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_age > ?");
preparedStatement.setInt(1,25);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int empId = resultSet.getInt("emp_id");
String empName = resultSet.getString("emp_name");
double empSalary = resultSet.getDouble("emp_salary");
int empAge = resultSet.getInt("emp_age");
System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
}
resultSet.close();
preparedStatement.close();
conn.close();
}

复制代码

// 新增
@Test
public void testInsert() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement preparedStatement = conn.prepareStatement("insert into t_tmp(emp_name,emp_salary,emp_age) values(?,?,?)");
preparedStatement.setString(1,"rose");
preparedStatement.setDouble(2,345.67);
preparedStatement.setInt(3,36);
int result = preparedStatement.executeUpdate();
// 根据受影响行数做判断，得到成功或失败
if (result>0){
System.out.println("成功！");
}else{
System.out.println("失败！");
}
preparedStatement.close();
conn.close();
}

复制代码

// 修改
@Test
public void testUpdate() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement preparedStatement = conn.prepareStatement("update t_tmp set emp_salary = ? where emp_id=?");
preparedStatement.setDouble(1,888.88);
preparedStatement.setInt(2,6);
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("成功");
}else{
System.out.println("失败");
}
preparedStatement.close();
conn.close();
}

复制代码

// 删除
@Test
public void testDelete() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
// 手写SQL语句
PreparedStatement preparedStatement = conn.prepareStatement("delete from t_tmp where emp_id=?");
preparedStatement.setInt(1,5);
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("成功");
}else{
System.out.println("失败");
}
preparedStatement.close();
conn.close();
}

复制代码

package JavaJDBCBase;// 在类中写入@Test，自动导入org.junit.Test，便不用重复写main函数import org.junit.Test;import java.sql.*;public class Demo3JDBCOperation { // 单行单列查询
@Test
public void testQuerySingleRowAndCol() throws SQLException {
// 1.注册驱动
// 2.获取连接
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection connection = DriverManager.getConnection(url, username, password);
// 3.预编译SQL语句得到PreparedStatement对象
PreparedStatement preparedStatement = connection.prepareStatement("select count(*) as count from t_tmp");
// 4.执行语句获取结果
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果，进行遍历,如果明确只有一个结果，那么resultSet至少要做一次next的判断，才能拿到我们要的列的结果
while (resultSet.next()) {
// 用下标获取
int anInt = resultSet.getInt(1);
System.out.println(anInt);
}
// 6.释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
// 单行多列查询
@Test
public void testQuerySingleRowAndCol2() throws SQLException {
// 1.注册驱动
// 2.获取链接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBC", "root", "954926928lcl");
// 3.预编译SQL语句获得PreparedStatement对象
PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_id = ?");
// 4.为占位符赋值，然后执行，并接受结果
preparedStatement.setInt(1,5);
ResultSet resultSet = preparedStatement.executeQuery();
// 5.处理结果
while (resultSet.next()) {
int empId = resultSet.getInt("emp_id");
String empName = resultSet.getString("emp_name");
double empSalary = resultSet.getDouble("emp_salary");
int empAge = resultSet.getInt("emp_age");
System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
}
// 6.资源释放
resultSet.close();
preparedStatement.close();
connection.close();
} // 多行多列查询
@Test
public void testQueryMoreRow() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement preparedStatement = conn.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_tmp where emp_age > ?");
preparedStatement.setInt(1,25);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int empId = resultSet.getInt("emp_id");
String empName = resultSet.getString("emp_name");
double empSalary = resultSet.getDouble("emp_salary");
int empAge = resultSet.getInt("emp_age");
System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
}
resultSet.close();
preparedStatement.close();
conn.close();
}
// 新增
@Test
public void testInsert() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement preparedStatement = conn.prepareStatement("insert into t_tmp(emp_name,emp_salary,emp_age) values(?,?,?)");
preparedStatement.setString(1,"rose");
preparedStatement.setDouble(2,345.67);
preparedStatement.setInt(3,36);
int result = preparedStatement.executeUpdate();
// 根据受影响行数做判断，得到成功或失败
if (result>0){
System.out.println("成功！");
}else{
System.out.println("失败！");
}
preparedStatement.close();
conn.close();
} // 修改
@Test
public void testUpdate() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement preparedStatement = conn.prepareStatement("update t_tmp set emp_salary = ? where emp_id=?");
preparedStatement.setDouble(1,888.88);
preparedStatement.setInt(2,6);
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("成功");
}else{
System.out.println("失败");
}
preparedStatement.close();
conn.close();
} // 删除
@Test
public void testDelete() throws SQLException {
String url = "jdbc:mysql://localhost:3306/JDBC";
// 用户名
String username = "root";
// 密码
String password = "954926928lcl";
// 获取链接对象
Connection conn = DriverManager.getConnection(url,username,password);
// 手写SQL语句
PreparedStatement preparedStatement = conn.prepareStatement("delete from t_tmp where emp_id=?");
preparedStatement.setInt(1,5);
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("成功");
}else{
System.out.println("失败");
}
preparedStatement.close();
conn.close();
}}

复制代码