目录
1.数据库连接及简单增删改查
a.导入jar包
创建一个lib目录,然后将MySQL的jar包粘贴到lib目录下,之后单击右键选择Add as Library选项,点击确定后就能导入jar包。
b.数据库连接及增删改查的具体实现
步骤:
1.加载驱动类(用反射的方式加载)
2. 获取连接
3. 获取数据库操作对象(命令执行对象)
4. 执行sql(增删改查)
5. 获取结果集对象(针对查询操作)
6. 释放连接
public class Test1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// System.out.println("123");
test1();
}
public static void test1() throws ClassNotFoundException, SQLException {
Driver driver = null;
// Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
// 连接数据库
String url = "jdbc:mysql://localhost:3306/0720_db?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
String user = "liu";
String password = "123456";
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection(url,user,password);
//添加
// String sql = "insert into bank" +
// "(account,money)" +
// "values" +
// "('小明',10000)";
//修改
// String sql = "update bank set account = '小绿'" +
// "where bid = 3";
//删除
// String sql = "delete from bank where bid = 3";
// statement = con.createStatement();
// int count = statement.executeUpdate(sql);
// System.out.println(count);
//查询
String sql = "select * from bank";
statement = con.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
for (int i = 1; i < 3; i++) {
System.out.print(resultSet.getObject(i)+" ");
}
System.out.println();
}
//关闭数据库
con.close();
}
2.junit包Test注释下的增删改查
a.导入junit 的jar包
步骤与MySQL的jar包导入一样
b.具体实现
package com.yzh.test2;
import org.junit.Test;
import java.sql.*;
public class MyTest {
private String drive = "com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/0720_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
private String user = "liu";
private String password = "123456";
@Test
public void insert(){
// System.out.println("插入数据");
Connection con = null;
Statement st = null;
String sql = "insert into bank" +
"(account,money)" +
"values" +
"('李四',200)";
try {
Class.forName(drive);
con = DriverManager.getConnection(url,user,password);
st = con.createStatement();
int count = st.executeUpdate(sql);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
@Test
public void update(){
// System.out.println("修改数据");
Connection con = null;
Statement st = null;
String sql = "update bank set account='小紫'" +
"where bid = 4";
try {
Class.forName(drive);
con = DriverManager.getConnection(url,user,password);
st = con.createStatement();
int count = st.executeUpdate(sql);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
@Test
public void delete(){
// System.out.println("删除数据");
Connection con = null;
Statement st = null;
String sql = "delete from bank where bid=4";
try {
Class.forName(drive);
con = DriverManager.getConnection(url,user,password);
st = con.createStatement();
int count = st.executeUpdate(sql);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
@Test
public void select(){
// System.out.println("插入数据");
Connection con = null;
Statement st = null;
ResultSet rs = null;
String sql = "select * from bank";
try {
Class.forName(drive);
con = DriverManager.getConnection(url,user,password);
st = con.createStatement();
rs = st.executeQuery(sql);
while (rs.next()){
for (int i = 1; i < 3; i++) {
System.out.print(rs.getObject(i)+" ");
}
System.out.println();
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}