package com.yxfyg.crud;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7
8 import org.junit.jupiter.api.Test;
9
10 import com.yxfyg.util.JDBCUtil;
11
12 public class TestCrud {
13
14 @Test
15 public void testQuery() {
16 Connection conn =
null;
17 Statement st =
null;
18 ResultSet rs =
null;
19 try {
20 conn =
JDBCUtil.getConn();
21 st =
conn.createStatement();
22 String sql = "select * from student"
;
23 rs =
st.executeQuery(sql);
24 while(rs.next()) {
25 int sno = rs.getInt("sno"
);
26 String sname = rs.getString("sname"
);
27 String sex = rs.getString("ssex"
);
28 int sage = rs.getInt("sage"
);
29 String sdept = rs.getString("sdept"
);
30 System.out.println("sno="+ sno +",sname="+ sname +",sex="+ sex +",age="+ sage +",sdept=" +
sdept);
31 }
32 }
catch (SQLException e) {
33 e.printStackTrace();
34 }
finally {
35 JDBCUtil.release(rs, st, conn);
36 }
37 }
38
39 @Test
40 public void testInsert() {
41 Connection conn =
null;
42 Statement st =
null;
43 try {
44 conn =
JDBCUtil.getConn();
45 st =
conn.createStatement();
46 String sql = "insert into student values(null,‘刘文‘,‘男‘,20,‘MA‘)"
;
47 //返回修改的行数
48 int result =
st.executeUpdate(sql);
49 if(result > 0
) {
50 System.out.println("添加成功"
);
51 }
else {
52 System.out.println("添加失败"
);
53 }
54 }
catch (SQLException e) {
55 e.printStackTrace();
56 }
finally {
57 JDBCUtil.release(st, conn);
58 }
59 }
60
61 @Test
62 public void testUpdate() {
63 Connection conn =
null;
64 Statement st =
null;
65 try {
66 conn =
JDBCUtil.getConn();
67 st =
conn.createStatement();
68 String sql = "update student set sname = ‘测试‘ where sno = 5"
;
69 //返回修改的行数
70 int result =
st.executeUpdate(sql);
71 if(result > 0
) {
72 System.out.println("更新成功"
);
73 }
else {
74 System.out.println("更新失败"
);
75 }
76 }
catch (SQLException e) {
77 e.printStackTrace();
78 }
finally {
79 JDBCUtil.release(st, conn);
80 }
81 }
82
83 @Test
84 public void testDelete() {
85 Connection conn =
null;
86 Statement st =
null;
87 try {
88 conn =
JDBCUtil.getConn();
89 st =
conn.createStatement();
90 String sql = "delete from student where sno = 5"
;
91 //返回修改的行数
92 int result =
st.executeUpdate(sql);
93 if(result > 0
) {
94 System.out.println("删除成功"
);
95 }
else {
96 System.out.println("删除失败"
);
97 }
98 }
catch (SQLException e) {
99 e.printStackTrace();
100 }
finally {
101 JDBCUtil.release(st, conn);
102 }
103 }
104 }
1 package com.yxfyg.util;
2
3 import java.io.FileInputStream;
4 import java.io.IOException;
5 import java.io.InputStream;
6 import java.sql.Connection;
7 import java.sql.DriverManager;
8 import java.sql.ResultSet;
9 import java.sql.SQLException;
10 import java.sql.Statement;
11 import java.util.Properties;
12
13 public class JDBCUtil {
14
15 static String url = null;
16 static String driverClass = null;
17
18 static {
19 try {
20 //1.创建一个属性配置对象
21 Properties properties = new Properties();
22 //2.使用类加载器去读取src底下的资源文件
23 InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
24 //若使用下面这种，properties文件要位于工程根目录
25 //InputStream is = new FileInputStream("jdbc.properties");
26 //3.导入输入流
27 properties.load(is);
28 //4.读取属性
29 driverClass = properties.getProperty("driverClass");
30 url = properties.getProperty("url");
31 } catch (IOException e) {
32 e.printStackTrace();
33 }
34 }
35
36 public static void release(ResultSet rs,Statement st,Connection conn) {
37 closeRs(rs);
38 closeSt(st);
39 closeConn(conn);
40 }
41
42 public static void release(Statement st,Connection conn) {
43 closeSt(st);
44 closeConn(conn);
45 }
46
47 public static Connection getConn() {
48 Connection conn = null;
49 try {
50 Class.forName(driverClass);
51 conn = DriverManager.getConnection(url);
52 } catch (Exception e) {
53 e.printStackTrace();
54 }
55 return conn;
56 }
57
58 private static void closeRs(ResultSet rs) {
59 try {
60 if(rs != null) {
61 rs.close();
62 }
63 }catch(SQLException e) {
64 e.printStackTrace();
65 }finally {
66 rs = null;
67 }
68 }
69
70 private static void closeSt(Statement st) {
71 try {
72 if(st != null) {
73 st.close();
74 }
75 }catch(SQLException e) {
76 e.printStackTrace();
77 }finally {
78 st = null;
79 }
80 }
81
82 private static void closeConn(Connection conn) {
83 try {
84 if(conn != null) {
85 conn.close();
86 }
87 }catch(SQLException e) {
88 e.printStackTrace();
89 }finally {
90 conn = null;
91 }
92 }
93 }
url=jdbc:mysql://localhost/mydb?user=root&password=root
driverClass=com.mysql.jdbc.Driver
JDBC对数据库数据进行增删改查
标签：try cut 配置 file value sql select create while
