纸绘武身（JDBC基础）

package JDBC0;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class

JDBC

{
public static void main(String[] args) {
listlook();
//log();
//work();
}
public static void

listlook()

//查询所有记录封装为Demo的list集合
{
List list = new JDBC().findAll();
System.out.println(list);
System.out.println(list.size());
}
public List

findAll()

//listlook()子类
{
ArrayList list=null;
Connection connection=null;
Statement statement=null;
ResultSet resultSet =null;

try {
// 注册驱动，获取连接，定义sql，获取执行sql的对象，执行sql，
Class.forName(“com.mysql.cj.jdbc.Driver”);
connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/db1?serverTimezone=GMT”, “root”, “root”);

String sql = “select * from demo”;
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
//int i = statement.executeUpdate(sql);i 为受影响的行数

Demo demo=null;
list = new ArrayList<>();
//获取数据并添加到对象
while (resultSet.next()){
int id=resultSet.getInt(“id”);
int num=resultSet.getInt(“num”);
demo = new Demo();
demo.setId(id);
demo.setNum(num);
//装载集合
list.add(demo);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
Tools.close(resultSet,statement,connection);

}
return list;
}
public static void

log()

{
//注册登录
Scanner scanner = new Scanner(System.in);
System.out.println(“用户名”);
String username = scanner.nextLine();
System.out.println(“密码”);
String password = scanner.nextLine();
Boolean flag = new JDBC().login(username, password);
if(flag){
System.out.println(“登录成功”);
}else {
System.out.println(“登录失败”);
}
}
public Boolean

login

//log的子类
(String username,String password){
if(usernamenull || passwordnull){
return false;
}
Connection connection=null;
PreparedStatement perparedstatement=null;
ResultSet resultSet =null;
try{
connection=Tools.getConnection();
String sql=“select * from user where username = ? and password = ?”;
perparedstatement=connection.prepareStatement(sql);
perparedstatement.setString(1,username);
perparedstatement.setString(2,password);
resultSet = perparedstatement.executeQuery();
return resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
Tools.close(resultSet,perparedstatement,connection);
}
return false;
}

public static void

work()

//控制事务
{
Connection connection=null;
PreparedStatement perparedstatement=null;
ResultSet resultSet =null;
try{
connection=Tools.getConnection();
connection.setAutoCommit(false);
String sql=“update demo set num = ? where id=1”;
perparedstatement=connection.prepareStatement(sql);
perparedstatement.setInt(1,2200);
perparedstatement.executeUpdate();
connection.commit();
} catch (SQLException throwables) {try {
if(connection!=null){
connection.rollback();
}
} catch (SQLException throwables1) {
throwables1.printStackTrace();
}
throwables.printStackTrace();
}finally {
Tools.close(null,perparedstatement,connection);
}

}
}//end
package JDBC0;

public class

Demo

{

private int id;
private int num;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public int getNum() {
return num;
}

public void setNum(int num) {
this.num = num;
}

@Override
public String toString() {
return “Demo{” +
“id=” + id +
“, num=” + num +
‘}’;
}
}
package JDBC0;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class

Tools

{
private static String url;
private static String user;
private static String password;
private static String driver;
static {
try{
//类加载器，基于src文件夹
ClassLoader classLoader = Tools.class.getClassLoader();
URL resource = classLoader.getResource(“jdbc.properties”);
String path = resource.getPath();

Properties properties = new Properties();
properties.load(new FileReader(path));
url=properties.getProperty(“url”);
user=properties.getProperty(“user”);
password=properties.getProperty(“password”);
driver=properties.getProperty(“driver”);
Class.forName(“com.mysql.cj.jdbc.Driver”);
} catch (FileNotFoundException e) {
e.printStackTrace();
} 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

(ResultSet resultSet, Statement Statement,Connection connection){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(Statement!=null){
try {
Statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}//end

jdbc.properties

url=jdbc:mysql://localhost:3306/db1?serverTimezone=GMT
user=root
password=root
diver=com.mysql.cj.jdbc.Driver