目录
一、JDBC编程
JDBC是
java
访问数据库的标准规范。
下载mysql
驱动包的链接:
https://dev.mysql.com/downloads/
连接数据库及操作数据库的步骤
1.注册和加载驱动
2.通过驱动的管理类获取数据库的连接
3.通过数据库连接获取语句对象
4.定义
sql
语句
5.通过语句对象执行
sql
6.判断结果
7.关闭连接,释放资源
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo01 {
public static void insertDemo() throws ClassNotFoundException, SQLException {
//注册加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//通过驱动的管理类获取数据库的连接
Connection connection = DriverManager.getConnection("jdbc:mysql://本地地址:端口号/数据库名?serverTimezone=UTC","用户名","密码");
//通过数据库连接获取数据库语句对象
Statement statement = connection.createStatement();
//创建sql语句 增加
//String sql = "insert into user_info values(null,'吓尿非',65)";
//删除
String sql = "delete from user_info where id = 19";
//修改
//String sql = "update user_info set user_name = '手动阀手动阀' where id = 17";
//执行sql
int result = statement.executeUpdate(sql);
//判断结果
if (result>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
//关闭连接,释放资源
statement.close();
connection.close();
}
public static void main(String[] args) {
try {
insertDemo();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.*;
import java.util.Scanner;
/**
*
* 1.基于JDBC编程,实现删除指定员工,要求员工编号键盘输入
*/
public class Demo02 {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:端口号/数据库名?serverTimezone=UTC";
private static String user = "用户名";
private static String password = "密码";
public static void deleteDemo(int num) {
Statement statement = null;
Connection connection = null;
try {
//注册加载驱动
Class.forName(driver);
//通过驱动的管理类获取数据库的连接
connection = DriverManager.getConnection(url, user, password);
//通过数据库连接获取数据库语句对象
statement = connection.createStatement();
//删除
String sql = "delete from user_info where id = "+num;
//执行sql
boolean result = statement.execute(sql);
//判断结果
if (result){
System.out.println("删除失败");
}else {
System.out.println("删除成功");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入id:");
int num = sc.nextInt();
deleteDemo(num);
}
}
import common.jdbcConfig;
import java.sql.*;
/**
*
* 1.基于JDBC编程,实现查询所有的员工姓名及员工年龄
*/
public class Demo03 {
public static void selectDemo() throws ClassNotFoundException, SQLException {
//注册加载驱动
Class.forName(jdbcConfig.driver);
//通过驱动的管理类获取数据库的连接
Connection connection = DriverManager.getConnection(jdbcConfig.url, jdbcConfig.user, jdbcConfig.password);
//通过数据库连接获取数据库语句对象
Statement statement = connection.createStatement();
//创建sql
String sql = "select * from emp";
//执行sql
ResultSet resultSet = statement.executeQuery(sql);
//判断结果
while(resultSet.next()){
int emp_no = resultSet.getInt("empno");
System.out.print(emp_no+" ");
String emp_name = resultSet.getString("ename");
System.out.println(emp_name);
}
//关闭连接,释放资源
resultSet.close();
statement.close();
connection.close();
}
public static void main(String[] args) {
try {
selectDemo();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import common.jdbcConfig;
import java.sql.*;
import java.util.Scanner;
/**
*
* 1.基于JDBC编程,实现用户登录
*/
public class Demo04 {
private static boolean flag = false;
public static boolean loginDemo(String username,String password) throws ClassNotFoundException, SQLException {
//注册加载驱动
Class.forName(jdbcConfig.driver);
//通过驱动的管理类获取数据库的连接
Connection connection = DriverManager.getConnection(jdbcConfig.url, jdbcConfig.user, jdbcConfig.password);
//通过数据库连接获取数据库语句对象
Statement statement = connection.createStatement();
//创建sql语句
String sql = "select * from user where user_name = '"+username+"' and password = '"+password+"'";
//执行sql
ResultSet resultSet = statement.executeQuery(sql);
//判断结果
if (resultSet.next()){
flag = true;
}
//关闭连接,释放资源
resultSet.close();
statement.close();
connection.close();
return flag;
}
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("请输入用户名:");
String username = sc.nextLine();
System.out.print("请输入密码:");
String password = sc.nextLine();
try {
boolean result = loginDemo(username,password);
if (result){
System.out.println("登陆成功");
}else {
System.out.println("登陆失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}