备注：try catch快捷键：选中代码块，然后Ctrl+Alt+T? ?//idea
所需文件夹
查询：statement.executeQuery(sql)? 返回结果是数据集
package com;
import java.sql.*;
public class Demo1 {
//备注：try catch快捷键：选中代码块，然后Ctrl+Alt+T
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//mysql8.0以后版本
//2.获得链接
String username = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/mysql2?serverTimezone=UTC";
connection = DriverManager.getConnection(url, username, passWord);
//3.定义sql，创建状态通道（进行sql语句的发送）
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from emp1");//executeQuery(sql) 执行查询
//4.取出结果集信息
while(resultSet.next()){//判断是否有下一条数据
//取出数据：resultSet.getXXX(“列名”); xxx表示数据类型
System.out.println("姓名："+resultSet.getString("ename")+",工资："+resultSet.getDouble("sal")+",雇佣日期:"+resultSet.getDate("hiredate"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
增删改：statement.executeUpdate(sql)? 返回结果是int类型，指受影响的行数
package com;
import java.sql.*;
public class Demo1 {
//备注：try catch快捷键：选中代码块，然后Ctrl+Alt+T
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//mysql8.0以后版本
//2.获得链接
String username = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/mysql2?serverTimezone=UTC";
connection = DriverManager.getConnection(url, username, passWord);
//3.定义sql，创建状态通道（进行sql语句的发送）
statement = connection.createStatement();
//返回结果为受影响的行数 result的值是受影响的行数
int result = statement.executeUpdate("insert into emp1(ename,hiredate,sal) values('aa','2020-1-1',2000)");//executeUpdate(sql) 执行增删改时使用
if(result>0){
System.out.println("执行成功");
}else{
System.out.println("执行失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
SQL注入
package com;
import java.sql.*;
public class Demo1 {
//备注：try catch快捷键：选中代码块，然后Ctrl+Alt+T
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//mysql8.0以后版本
//2.获得链接
String username = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/mysql2?serverTimezone=UTC";
connection = DriverManager.getConnection(url, username, passWord);
//3.定义sql，创建状态通道（进行sql语句的发送）
statement = connection.createStatement();
String uname = "abcd";
String pass = " '' or 1=1";
resultSet = statement.executeQuery("select * from users2 where username='"+uname+"' and password="+pass);//executeQuery(sql) 执行查询
//4.取出结果集信息
if(resultSet.next()){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
防止SQL注入（预状态通道）
package com;
import java.sql.*;
public class Demo1 {
//备注：try catch快捷键：选中代码块，然后Ctrl+Alt+T
public static void main(String[] args){
Connection connection = null;
PreparedStatement pps = null;
ResultSet resultSet = null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//mysql8.0以后版本
//2.获得链接
String username = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/mysql2?serverTimezone=UTC";
connection = DriverManager.getConnection(url, username, passWord);
//3.定义sql，创建预状态通道（进行sql语句的发送）
String sql = "select * from users2 where username=? and password=?";
pps = connection.prepareStatement(sql);
String uname = "aa";
String pass = "1";
//给占位符赋值 (下标，内容)
pps.setString(1,uname);
pps.setString(2,pass);
//执行sql
resultSet = pps.executeQuery();
//4.取出结果集信息
if(resultSet.next()){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5.关闭资源
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
?