1.数据库连接的五种方式
import com.mysql.jdbc.Driver;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author ym
* @create 2022-01-29 19:35
* @description
*/
public class Connection {
/**
* 测试连接
*
* @throws SQLException
*/
@Test
public void testConnection1() throws SQLException {
//数据库驱动自带的连接
Driver driver = new Driver();
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "abc123");
java.sql.Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
/**
* 测试连接2,是对方式一的迭代,在如下的程序中不出现第三方的api,使得程序具有更好的可移植性
*
* @throws SQLException
*/
@Test
public void testConnection2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
//数据库驱动自带的连接
Class aClass = Class.forName("com.mysql.jdbc.Driver");
java.sql.Driver driver = (java.sql.Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "abc123");
java.sql.Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
/**
* 测试连接3,使用DriverManager 替换driver
*
* @throws SQLException
*/
@Test
public void testConnection3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
//数据库驱动自带的连接
Class aClass = Class.forName("com.mysql.jdbc.Driver");
java.sql.Driver driver = (java.sql.Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "abc123");
DriverManager.registerDriver(driver);
java.sql.Connection connection = DriverManager.getConnection(url, properties);
System.out.println(connection);
}
/**
* 测试连接4,进一步优化,使得驱动无需注册
*
* @throws SQLException
*/
@Test
public void testConnection4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
//数据库驱动自带的连接
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "abc123");
//此处无需注册的原因是驱动的源码里有一静态代码块,此处使得驱动无需注册
// static {
// try {
// DriverManager.registerDriver(new Driver());
// } catch (SQLException var1) {
// throw new RuntimeException("Can't register driver!");
// }
// }
java.sql.Connection connection = DriverManager.getConnection(url, properties);
System.out.println(connection);
}
/**
* 测试连接5,使用配置文件
*
* @throws SQLException
*/
@Test
public void testConnection5() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException {
InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
//数据库驱动自带的连接
Class.forName(properties.getProperty("driverClass"));
java.sql.Connection connection = DriverManager.getConnection(properties.getProperty("url"),
properties.getProperty("user"), properties.getProperty("password"));
System.out.println(connection);
}
}
2.使用preparedStatement进行增删改
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
/**
* @author ym
* @create 2022-01-29 21:39
* @description 测试增删改等方法
*/
public class InsertTest {
@Test
public void testInsert() {
//1.数据库连接
InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
Properties properties = new Properties();
java.sql.Connection connection = null;
PreparedStatement preparedStatement = null;
try {
properties.load(resourceAsStream);
//数据库驱动自带的连接
Class.forName(properties.getProperty("driverClass"));
connection = DriverManager.getConnection(properties.getProperty("url"),
properties.getProperty("user"), properties.getProperty("password"));
//2.预编译sql语句
String sqlString = "insert into customers(name,email,birth) values(?,?,?)";
preparedStatement = connection.prepareStatement(sqlString);
//3.填充占位符
preparedStatement.setString(1, "张飞");
preparedStatement.setString(2, "zhangfei@163.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date parseDate = simpleDateFormat.parse("1000-01-01");
preparedStatement.setDate(3, new java.sql.Date(parseDate.getTime()));
//4.执行操作
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} finally {
//5.关闭资源
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3.建立DBUtil工具类,并且实现一个修改操作
DBUtil工具类:
import com.mysql.jdbc.Connection;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author ym
* @create 2022-01-29 22:00
* @description
*/
public class DBUtil {
/**
* 获取连接
*
* @return
* @throws IOException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static java.sql.Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
//数据库驱动自带的连接
Class.forName(properties.getProperty("driverClass"));
java.sql.Connection connection = DriverManager.getConnection(properties.getProperty("url"),
properties.getProperty("user"), properties.getProperty("password"));
System.out.println(connection);
return connection;
}
/**
* 关闭资源
*
* @param connection
* @param statement
*/
public static void closeResource(java.sql.Connection connection, Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
修改操作:
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
/**
* @author ym
* @create 2022-01-29 21:39
* @description 测试增删改等方法
*/
public class InsertTest {
@Test
public void testInsert() {
//1.数据库连接
InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
Properties properties = new Properties();
java.sql.Connection connection = null;
PreparedStatement preparedStatement = null;
try {
properties.load(resourceAsStream);
//数据库驱动自带的连接
Class.forName(properties.getProperty("driverClass"));
connection = DriverManager.getConnection(properties.getProperty("url"),
properties.getProperty("user"), properties.getProperty("password"));
//2.预编译sql语句
String sqlString = "insert into customers(name,email,birth) values(?,?,?)";
preparedStatement = connection.prepareStatement(sqlString);
//3.填充占位符
preparedStatement.setString(1, "张飞");
preparedStatement.setString(2, "zhangfei@163.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date parseDate = simpleDateFormat.parse("1000-01-01");
preparedStatement.setDate(3, new java.sql.Date(parseDate.getTime()));
//4.执行操作
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} finally {
//5.关闭资源
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Test
public void testUpdate() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1.获取连接
connection = DBUtil.getConnection();
//2.预编译sql语句
String sql = "update customers set name = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
preparedStatement.setObject(1, "莫扎特");
preparedStatement.setObject(2, 18);
//4.执行
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
//5.关闭资源
DBUtil.closeResource(connection, preparedStatement);
}
}