JDBC
JDBC
注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
连接数据库
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai","root","admin");
定义SQL
String sql="select * from Student";
执行SQL
Statement stmt=stmt.executeQuery(sql);
遍历结果 封装对象
Student s=null;
List<Student> list=new ArrayList<Student>();
while (rs.next()){
//获取数据
String Sno=rs.getString("Sno");
String Sname = rs.getString("Sname");
String Ssex = rs.getString("Ssex");
int Sage = rs.getInt("Sage");
String Sdept = rs.getString("Sdept");
s=new Student();
s.setSno(Sno);
s.setSname(Sname);
s.setSsex(Ssex);
s.setSage(Sage);
s.setSdept(Sdept);
list.add(s);
}
释放对象
finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
-
注意
conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useSSL=false","root","admin");其中&useSSL=false可以使得idea不报以下错误
Fri Nov 19 19:42:51 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verifi
抽取JDBC工具类: JDBCUtils
-
提取注册驱动
-
提取释放的部分
public class JDBCUtils { private static String user; private static String url; private static String password; private static String driver; static { //1.创建一个Properties集合类 Properties pro = new Properties(); //加载文件 try { //获取绝对路径 ClassLoader classLoader=JDBCUtils.class.getClassLoader(); URL res=classLoader.getResource("jdbcUtils.properties"); pro.load(new FileReader(res.getPath())); //赋值 url=pro.getProperty("url"); user=pro.getProperty("user"); password=pro.getProperty("password"); driver=pro.getProperty("driver"); //注册驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //抽取获取链接的方法 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,user,password); } //释放资源 public static void close(Connection conn, Statement stmt){ if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs,Connection conn, Statement stmt) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
test-JDBCUtils
public class jdbc_3 {
public static void main(String[] args) {
List<Student> list=new jdbc_3().findAll2();
System.out.println(list);
}
public List<Student> findAll(){
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//连接数据库基础
conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useSSL=false","root","nxylyy");
stmt=conn.createStatement();
String sql="select * from Student";
rs =stmt.executeQuery(sql);
Student s=null;
List<Student> list=new ArrayList<Student>();
while (rs.next()){
String Sno=rs.getString("Sno");
String Sname = rs.getString("Sname");
String Ssex = rs.getString("Ssex");
int Sage = rs.getInt("Sage");
String Sdept = rs.getString("Sdept");
s=new Student();
s.setSno(Sno);
s.setSname(Sname);
s.setSsex(Ssex);
s.setSage(Sage);
s.setSdept(Sdept);
list.add(s);
}
return list;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
public List<Student> findAll2(){
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
//注册驱动
/* Class.forName("com.mysql.cj.jdbc.Driver");
//连接数据库基础
conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useSSL=false","root","nxylyy");*/
conn= JDBCUtils.getConnection();
stmt=conn.createStatement();
String sql="select * from Student";
rs =stmt.executeQuery(sql);
Student s=null;
List<Student> list=new ArrayList<Student>();
while (rs.next()){
String Sno=rs.getString("Sno");
String Sname = rs.getString("Sname");
String Ssex = rs.getString("Ssex");
int Sage = rs.getInt("Sage");
String Sdept = rs.getString("Sdept");
s=new Student();
s.setSno(Sno);
s.setSname(Sname);
s.setSsex(Ssex);
s.setSage(Sage);
s.setSdept(Sdept);
list.add(s);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
/* if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}*/
JDBCUtils.close(rs,conn,stmt);
}
return null;
}
}
jdbc 登录案例
public class login {
//初步实现登录 通过键盘读入用户名密码判断
public static void main(String[] args) {
//1.输入
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名:");
String user=sc.nextLine();
System.out.println("请输入密码:");
String password=sc.nextLine();
//调用方法
boolean flag= new login().loginon(user, password);
//判断结果
if(flag) System.out.println("登陆成功");
else System.out.println("账号或者密码出错");
}
public boolean loginon(String username,String password ) {
Statement stmt=null;
ResultSet rs=null;
Connection conn=null;
if(username==null||password==null)
return false;
else{
try {
//获取连接
conn= JDBCUtils.getConnection();
// 定义sql
String sql="select * from user where username='"+username+"' and password='"+password+"'";
//创建执行sql的对象
stmt=conn.createStatement();
//执行sql
rs=stmt.executeQuery(sql);
//结果判断
/* if(rs.next())
return true;
else return false;*/
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,conn,stmt);
}
}
return false;
}
}
- 以上方法存在严重的安全问题,通过SQL注入的方式不需要密码可直接登录.
- Statement 静态sql存在安全隐患 现在采用PrepareStatement 采用预编译的方式 参数采用?占位符
改进后的登录案例
public boolean loginon2(String username,String password ) {
PreparedStatement pstmt=null;
ResultSet rs=null;
Connection conn=null;
if(username==null||password==null)
return false;
else{
try {
//获取连接
conn= JDBCUtils.getConnection();
// 定义sql
String sql="select * from user where username= ? and password= ?";
// System.out.println(sql);
//创建执行sql的对象
pstmt=conn.prepareStatement(sql);
//给问号赋值
pstmt.setString(1,username);
pstmt.setString(2,password);
//执行sql
rs=pstmt.executeQuery();
//结果判断
/* if(rs.next())
return true;
else return false;*/
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,conn,pstmt);
}
}
return false;
}
- 可以用Statement对象释放PrepareStatement对象
JDBC控制事务
-
使用Connection对象 管理实务
- 开启事务 setAutoCommit(boollean autoCommit):调用该方法设置事务参数为false 即开启事务
- 提交事务:commit()
- 回滚事务:rollback()
- 在执行sql之前开启事务
- 所有sql执行完毕没有异常发生 提交事务
public static void main(String[] args) { Connection conn=null; PreparedStatement pstmt1=null; PreparedStatement pstmt2=null; try { conn= JDBCUtils.getConnection(); //开启事务 conn.setAutoCommit(false); String sql1="update account set Sage=Sage-? where id= ?"; String sql2="update account set Sage=Sage+? where id= ?"; pstmt1=conn.prepareStatement(sql1); pstmt2=conn.prepareStatement(sql2); pstmt1.setInt(1,500); pstmt1.setInt(2,1); pstmt2.setInt(1,500); pstmt2.setInt(2,2); pstmt1.execute(); //手动制造异常 int i=3/0; pstmt2.execute(); conn.commit(); } catch (Exception e) { if(conn!=null) { try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } e.printStackTrace(); }finally { JDBCUtils.close(conn,pstmt1); JDBCUtils.close(conn,pstmt2); } }
idea快速注释
- Ctrl+/ 注释光标当前所在行 重复操作取消注释
- Ctrl+shift+/块注释(一次注释多行) 一次注释掉选中行 重复操作取消注释
- 在一个方法或类的开头，输入/，然后按回车,自动根据参数和返回值生成注释模板**，我们在这个模板上面编写即可。
-
码云创建图床test
-
码云创建图床test
