Connection conn = DriverManager.getConnection(url,username,password);
// url=jdbc:mysql://localhost:3306/jdbcdemo
// 如果連接的是本機(jī)的 MySQL,并且端口是默認(rèn)的 3306 ,則可以簡寫:
//  url=jdbc:mysql:///jdbcdemo
// username:當(dāng)前訪問數(shù)據(jù)庫的用戶名
// password:當(dāng)前訪問數(shù)據(jù)庫的密碼

@Test
    public void testConnection4() {
        try {
            //1.數(shù)據(jù)庫連接的4個基本要素：
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "admin";
            String driverName = "com.mysql.jdbc.Driver";

            //2.加載驅(qū)動 （①實(shí)例化Driver ②注冊驅(qū)動）
            Class.forName(driverName);

            //3.獲取連接
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

@Test
    public  void testConnection5() throws Exception {
    	//1.加載配置文件
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2.讀取配置信息
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3.加載驅(qū)動
        Class.forName(driverClass);

        //4.獲取連接
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }

cn.util;    //存放工具類
   cn.domain; //裝pss模塊的domain類,模型對象.(Student)
   cn.dao; //裝pss模塊的dao接口.調(diào)用者將需要保存的數(shù)據(jù)封裝到一個對象中,然后傳遞進(jìn)來
    cn.dao.impl; //裝dao接口的實(shí)現(xiàn)類.
   cn.test; //暫時存儲DAO的測試類,以后的測試類不應(yīng)該放這里.

int excuteUpdate(String sql)：執(zhí)行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql)：執(zhí)行查詢操作SELECT

public class StatementTest {

	// 使用Statement的弊端：需要拼寫sql語句，并且存在SQL注入的問題
	@Test
	public void testLogin() {
		Scanner scan = new Scanner(System.in);

		System.out.print("用戶名：");
		String userName = scan.nextLine();
		System.out.print("密   碼：");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
				+ "'";//字符串拼接過于繁雜
		User user = get(sql, User.class);
		if (user != null) {
			System.out.println("登陸成功!");
		} else {
			System.out.println("用戶名或密碼錯誤！");
		}
	}

	// 使用Statement實(shí)現(xiàn)對數(shù)據(jù)表的查詢操作
	public <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加載配置文件
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2.讀取配置信息
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

			// 3.加載驅(qū)動
			Class.forName(driverClass);

			// 4.獲取連接
			conn = DriverManager.getConnection(url, user, password);

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// 獲取結(jié)果集的元數(shù)據(jù)
			ResultSetMetaData rsmd = rs.getMetaData();

			// 獲取結(jié)果集的列數(shù)
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. 獲取列的名稱
					// String columnName = rsmd.getColumnName(i+1);

					// 1. 獲取列的別名
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. 根據(jù)列名獲取對應(yīng)數(shù)據(jù)表中的數(shù)據(jù)
					Object columnVal = rs.getObject(columnName);

					// 3. 將數(shù)據(jù)表中得到的數(shù)據(jù)，封裝進(jìn)對象
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 關(guān)閉資源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return null;
	}
}

// 常用方法:
void setXxx(int parameterIndex,Xxx value); //設(shè)置第幾個占位符的真正參數(shù)值.
// Xxx 表示數(shù)據(jù)類型,比如 String,int,long,Date等.
void setObject(int parameterIndex, Object x); //設(shè)置第幾個占位符的真正參數(shù)值.
int executeUpdate(); //執(zhí)行DDL/DML語句. 注意:沒有參數(shù)
// 若當(dāng)前 SQL是 DDL語句,則返回 0.
// 若當(dāng)前 SQL是 DML語句,則返回受影響的行數(shù).
ResultSet executeQuery(); //執(zhí)行DQL語句,返回結(jié)果集.
close(); //釋放資源

getInt(1), getString("name")

package domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 19:44
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
  //id
  private Integer id;
  //用戶名
  private String username;
  //密碼
  private String pwd;

  //構(gòu)造方法的重載
  public User(String username, String pwd) {
    this.username = username;
    this.pwd = pwd;
  }
}

package dao.impl;

import dao.IUserDAO;

import dao.utils.DaoUtils;
import domain.User;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 19:49
 */

public class UserDAOImpl implements IUserDAO {
  Connection connection = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  @Override
  public void insert(User user) {
    try {
      connection = DaoUtils.getConnection();
      String sql = "insert into user (username,pwd) values (?,?)";
       ps = connection.prepareStatement(sql);
      ps.setString(1,user.getUsername());
      ps.setString(2,user.getPwd());
      ps.executeUpdate();
      System.out.println("添加成功！！");
    } catch (SQLException e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,null);
    }
  }

  @Override
  public void delete(Integer id) {
    try {
      connection = DaoUtils.getConnection();
      ps = connection.prepareStatement("delete from user where id = ?");
      ps.setInt(1,id);
      ps.executeUpdate();
      System.out.println("刪除成功！！");

    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,null);
    }
  }

  @Override
  public void update(User user) {
    try {
      connection = DaoUtils.getConnection();
       ps = connection.prepareStatement("update user set username = ? , pwd = ? where id = ?");
       ps.setString(1,user.getUsername());
       ps.setString(2,user.getPwd());
       ps.setInt(3,user.getId());
      ps.executeUpdate();
      System.out.println("修改成功");
    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,null);
    }

  }

  @Override
  public List<User> selectAll() {
    List<User> users = new ArrayList<>();
    try {
      connection = DaoUtils.getConnection();
      ps = connection.prepareStatement("select * from user");
      rs = ps.executeQuery();
      while (rs.next()){
        users.add(new User(rs.getInt("id"),rs.getString("username"),rs.getString("pwd")));
      }

    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,rs);
    }
    return users;
  }

  @Override
  public User selectUserById(Integer id) {
    User user = null;
    try {
      connection = DaoUtils.getConnection();
      ps = connection.prepareStatement("select * from user where id = ?");
      ps.setInt(1,id);
      rs = ps.executeQuery();
      while (rs.next()){
        user = new User(rs.getInt("id"),rs.getString("username"),rs.getString("pwd"));
      }

    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      DaoUtils.close(connection,ps,rs);
    }
    return user;
  }
}

package dao.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 19:56
 */
public class DaoUtils {
  static Connection connection = null;
  static Properties properties = null;

  static {
    InputStream resourceAsStream = Thread.currentThread().getContextClassLoader()
        .getResourceAsStream("db.properties");
    properties = new Properties();
    try {
      properties.load(resourceAsStream);
      Class.forName(properties.getProperty("DriverClassName"));
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static Connection getConnection() throws SQLException {
   connection= DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
    return connection;
  }

  public  static void  close(Connection conn , PreparedStatement ps , ResultSet rs){

    try {
      if(rs!=null){
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }finally{
      try {
        if(ps!=null){
          ps.close();
        }
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }finally{
        try {
          if(conn!=null){
            conn.close();
          }
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
}

package dao.impl;


import dao.IUserDAO;
import dao.utils.DaoUtils;
import domain.User;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;

/**
 * @author Xiao_Lin
 * @date 2021/1/2 20:11
 */
public class UserDAOImplTest {
  IUserDAO userDAO = new UserDAOImpl();
  @Test
  public void insert() {
    userDAO.insert(new User("ghy","123"));

  }

  @Test
  public void delete() {
    userDAO.delete(14);
  }

  @Test
  public void update() {
    userDAO.update(new User(1,"張三","666"));
  }

  @Test
  public void selectAll() {
    List<User> users = userDAO.selectAll();
    users.forEach(System.out::print);
  }

  @Test
  public void selectUserById() {
    User user = userDAO.selectUserById(1);
    System.out.println(user);
  }
}

//獲取連接
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
ps.setString(1, "張三");
ps.setString(2, "zs@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob類型的變量
FileInputStream fis = new FileInputStream("zs.png");
ps.setBlob(4, fis);
//執(zhí)行
ps.execute();
		
fis.close();
JDBCUtils.closeResource(conn, ps);

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
// 操作Blob類型的變量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);

ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
	Integer id = rs.getInt(1);
    String name = rs.getString(2);
	String email = rs.getString(3);
    Date birth = rs.getDate(4);
	Customer cust = new Customer(id, name, email, birth);
    System.out.println(cust); 
    //讀取Blob類型的字段
	Blob photo = rs.getBlob(5);//這里也可以通過列的索引來讀取
	InputStream is = photo.getBinaryStream();
	OutputStream os = new FileOutputStream("c.jpg");
	byte [] buffer = new byte[1024];
	int len = 0;
	while((len = is.read(buffer)) != -1){
		os.write(buffer, 0, len);
	}
    JDBCUtils.closeResource(conn, ps, rs);
		
	if(is != null){
		is.close();
	}
		
	if(os !=  null){
		os.close();
	}
    
}

SELECT * FROM account WHERE name = '張無忌' AND balance >= 1000;

UPDATE account SET balance = balance - 1000 WHERE name = '張無忌';

UPDATE account SET balance = balance + 1000 WHERE name = '趙敏';

import org.junit.Test;

@Test
public void testTx() throws Exception {
// 賈璉欲執(zhí)事
// 1 查詢張無忌的賬戶余額是否大于等于1000
    Connection conn = JDBCUtil.getConnection();
    String sql = "SELECT * FROM account WHERE balance>=? AND name=?";
    PreparedStatement pst = conn.prepareStatement(sql);
// 給 ? 設(shè)置數(shù)據(jù)
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"張無忌");
    ResultSet rs = pst.executeQuery();
    if(!rs.next()){
    System.out.println("余額不足");
    return;
    }
// 2 從張無忌的賬戶余額中減少1000.
    sql = "UPDATE account SET balance = balance-? WHERE name=?";
    pst = conn.prepareStatement(sql);
//設(shè)置? 的數(shù)據(jù)
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"張無忌");
    pst.executeUpdate();
// 模擬出異常
    int a = 10/0;
// 3 在趙敏的賬戶余額中增加1000.
    sql = "UPDATE account SET balance = balance+? WHERE name=?";
    pst = conn.prepareStatement(sql);
//設(shè)置? 的數(shù)據(jù)
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"趙敏");
    pst.executeUpdate();
// 釋放資源
    JDBCUtil.close(conn,pst,rs);
    }

SELECT @@tx_isolation;

set transaction isolation level read committed;

set global transaction isolation level read committed;

try{
	//取消事務(wù)的自動提交機(jī)制,設(shè)置為手動提交.
	connection對象.setAutoCommit(false);
	//操作1
	//操作2
	//異常
	//操作3
	//....
	//手動提交事務(wù)
	connection對象.commit();
}catch(Exception e){
	//處理異常
    //回滾事務(wù)
connection對象.rollback();
}

//使用dbcp數(shù)據(jù)庫連接池的配置文件方式，獲取數(shù)據(jù)庫的連接：推薦
// 創(chuàng)建一個DataSource對象
private static DataSource source = null;
static{
	try {
        //創(chuàng)建一個Properties，用于讀取配置文件
		Properties pros = new Properties();
        //讀取配置文件
		InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("db.properties");
		//加載配置文件
		pros.load(is);
		//根據(jù)提供的BasicDataSourceFactory創(chuàng)建對應(yīng)的DataSource對象
		source = BasicDataSourceFactory.createDataSource(pros);
	} catch (Exception e) {
		e.printStackTrace();
	}
		
}
public static Connection getConnection4() throws Exception {
	Connection conn = source.getConnection();
	return conn;
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=123456

initialSize=10
#...

package com.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;

/**
 * @author Xiao_Lin
 * @date 2021/1/3 19:47
 */
public class DruidUtils {
  static DataSource ds = null;
  private DruidUtils(){

  }

  static {
    InputStream stream = Thread.currentThread().getContextClassLoader()
        .getResourceAsStream("db.properties");
    Properties properties = new Properties();
    try {
      properties.load(stream);
      ds = DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static Connection getConnection(){
    try {
      return ds.getConnection();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
}

DriverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///db?characterEncoding=utf-8&useSSL=false
username = root
password = 123456