1、反射......
2、JDBC
本质:一套接口
1、步骤:
1)、注册驱动(作用:告诉java程序 即将连接的是哪一款数据库)
// 2、获取连接
String url = "jdbc:mysql://127.0.0.1/bjpowernode";
String user = "root";
String password = "20020326";
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println("数据库连接对象=" + conn);
2)、获取连接(表示JVM的进程和数据库之间的通道打开了 使用完毕后关闭通道)
3)、获取数据库操作对象(专门执行sql语句的对象)
4)、执行sql语句 (DQL DML)
jdbc执行的sql语句不需要写分号" ; "
// 2、获取连接
String url = "jdbc:mysql://127.0.0.1/bjpowernode";
String user = "root";
String password = "20020326";
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println("数据库连接对象=" + conn);
public static void main(String[] args) {
try {
// 1、注册驱动第一种方式
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
// 注册驱动第二种方式
Class.forName("com.mysql.cj.jdbc.Driver");
// 2、获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","20020326");
System.out.println(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
url 统一资源定位符(网络中某个资源的绝对路径) ex:https://www.bauidu.com
URL包括 协议 IP PORT 资源名
1、https://182.61.200.7:80/index.html
2、https://通信协议
3、182.61.200.7:服务器IP地址
4、80 服务器上软件的端口
index.html 服务器上某个资源名
*** 从属性资源文件中 读取连接数据库信息
//properties配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode
user=root
password=20020326
public static void main(String[] args) {
//使用资源绑定器 绑定
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn = null;
Statement stmt = null;
try {
// 1、注册驱动
Class.forName(driver);
// 2、获取连接
conn = DriverManager.getConnection(url, user, password);
// 3、获取数据库对象
stmt = conn.createStatement();
// 4、执行sql语句
String sql = "update dept set dname ='销售部2',loc='青岛'where deptno =20";
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "修改成功" : "修改失败");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
5)、处理查询结果集 (只有当第四步执行的是select语句时 才有第五步查询结果集)
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
try {
// 1、注册驱动
Class.forName(driver);
// 2、获取连接
conn = DriverManager.getConnection(url, user, password);
// 3、获取数据库对象
stmt = conn.createStatement();
// 4、执行sql语句
String sql = "select empno,ename,sal from emp";
//executeQuery() 执行查询语句
//返回结果集
rs = stmt.executeQuery(sql);
// 5、处理查询结果集
boolean result = rs.next();
while (rs.next() == true) {
//getString() 方法:不管数据库中数据是什么类型 都以String类型取出
//JDBC中所有下标从1开始
String empno = rs.getString("empno"); //此处字段名 是查询结果集中的字段名 不是表中的字段名
String ename = rs.getString("ename");
String sal = rs.getString("sal");
System.out.println(empno + "," + ename + "," + sal);
//可以以特定类型取出 但要符合情况
// int empno1= rs.getInt("empno");
// String ename1 = rs.getString("ename");
// double sal1 = rs.getDouble("sal");
// System.out.println(empno1 + "," + ename1 + "," +sal1);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
6)、释放资源 (使用完资源后 要关闭资源)
2、用户登录
public static void main(String[] args) {
//初始化一个界面
Map<String, String> userloginInfo = intiUI();
//验证用户名+密码
boolean loginSuccess = login(userloginInfo);
//最后输出结果
System.out.println(loginSuccess? "登录成功" : "登录失败");
}
/*用户登录
* userloginInfo 用户登录信息
* false 登录失败 true 登录成功*/
private static boolean login(Map<String, String> userloginInfo) {
//JDBC代码
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//打标记
boolean loginSuccess = false;
//单独定义变量 便于sql语句读取用户名+密码
String loginName = userloginInfo.get("loginName");
String password = userloginInfo.get("password");
//1、注册驱动
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password1 = bundle.getString("password");
try {
Class.forName(driver);
//2、获取连接
conn = DriverManager.getConnection(url,user,password1);
//3、获取数据库对象
stmt = conn.createStatement();
//4、执行sql语句
//将一个变量加入字符串中 需要加一个双引号 双引号之间再加两个加号 两个加号之间加变量名
String sql = "select * from t_user where loginName = '"+loginName+"' and password = '"+password+"'";
rs = stmt.executeQuery(sql);
//5、处理结果集
if (rs.next()==true){
loginSuccess = true;
}
//6、释放资源
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if (rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return loginSuccess;
}
/*初始化用户界面
* return 用户输入的用户名+密码等登录信息*/
private static Map<String, String> intiUI() {
Scanner scanner = new Scanner(System.in);
System.out.println("用户名:");
String loginName = scanner.nextLine();
System.out.println("密码: ");
String password = scanner.nextLine();
Map<String, String> userloginInfo = new HashMap<>();
userloginInfo.put("loginName",loginName);
userloginInfo.put("password",password);
return userloginInfo;
}
>>1、解决sql注入
PreparedStatement:
1、解决sql注入问题
2、Statement 是编译一次 执行一次 PreparedStatement 是编译一次 执行N次 效率较高
3、PrepareStatement 会在编译阶段 进行类型检查 不符合条件的会报错
Statement :
1、凡是业务需要进行sql语句拼接的 必须使用Statement
public static void main(String[] args) {
//初始化一个界面
Map<String, String> userloginInfo = intiUI();
//验证用户名+密码
boolean loginSuccess = login(userloginInfo);
//最后输出结果
System.out.println(loginSuccess ? "登录成功" : "登录失败");
}
/*用户登录
* userloginInfo 用户登录信息
* false 登录失败 true 登录成功*/
private static boolean login(Map<String, String> userloginInfo) {
//JDBC代码
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//打标记
boolean loginSuccess = false;
//单独定义变量 便于sql语句读取用户名+密码
String loginName = userloginInfo.get("loginName");
String password = userloginInfo.get("password");
//1、注册驱动
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password1 = bundle.getString("password");
try {
Class.forName(driver);
//2、获取连接
conn = DriverManager.getConnection(url, user, password1);
//3、获取预编译的数据库对象
// ? 占位符 一个? 表示一个值 sql语句框子
String sql = "select * from t_user where loginName = ? and password = ?";
//程序执行到此处 会发送sql语句框子给DBMS 然后DBMS会进行sql语句的预编译
ps = conn.prepareStatement(sql);
//给占位符传值
ps.setString(1,loginName);
ps.setString(2,password);
//4、执行sql语句
rs = ps.executeQuery();
//5、处理结果集
if (rs.next()) {
loginSuccess = true;
}
//6、释放资源
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return loginSuccess;
}
/*初始化用户界面
* return 用户输入的用户名+密码等登录信息*/
private static Map<String, String> intiUI() {
Scanner scanner = new Scanner(System.in);
System.out.println("用户名:");
String loginName = scanner.nextLine();
System.out.println("密码: ");
String password = scanner.nextLine();
Map<String, String> userloginInfo = new HashMap<>();
userloginInfo.put("loginName", loginName);
userloginInfo.put("password", password);
return userloginInfo;
}
>>2、工具类
public class DBUtil {
/*工具类中的构造方法都是私有的
* 工具类中的方法都是私有的 不需要new对象 直接类名调用
* */
private DBUtil(){}
//静态代码块在类加载时执行 并且只执行一次
static{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//region获取数据库连接对象
/*
* return 连接对线*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","20020326");
}
//endregion
//region关闭资源
/*
* conn 连接对象
* ps 数据库操作对象
* rs 结果集*/
public static void close(Connection conn, Statement ps, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//endregion
}
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//获取连接
conn = DBUtil.getConnection();
//获取预编译的数据库操作对象
String sql = "select ename from emp where ename like ?";
ps = conn.prepareStatement(sql);
ps.setString(1,"_A%");
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("ename"));
}
} catch (Exception throwables) {
throwables.printStackTrace();
}finally {
//释放资源
DBUtil.close(conn,ps,rs);
}
}
3.悲观锁 + 乐观锁(浅识)
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
//开启事务
conn.setAutoCommit(false);
String sql = "select ename,job,sal from emp where job = ? for update";
ps = conn.prepareStatement(sql);
ps.setString(1, "manager");
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("ename") + "," + rs.getString("job") + "," + rs.getDouble("sal"));
}
//提交事务(事务结束)
conn.commit();
} catch (SQLException throwables) {
if (conn != null) {
try {
//回滚事务
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
throwables.printStackTrace();
} finally {
DBUtil.close(conn, ps, rs);
}
}