1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.ibatis.jdbc;
17
18 import static org.junit.jupiter.api.Assertions.assertEquals;
19 import static org.junit.jupiter.api.Assertions.assertTrue;
20
21 import java.sql.Connection;
22 import java.util.List;
23 import java.util.Map;
24
25 import javax.sql.DataSource;
26
27 import org.apache.ibatis.BaseDataTest;
28 import org.junit.jupiter.api.Test;
29
30 class SqlRunnerTest extends BaseDataTest {
31
32 @Test
33 void shouldSelectOne() throws Exception {
34 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
35 runScript(ds, JPETSTORE_DDL);
36 runScript(ds, JPETSTORE_DATA);
37 try (Connection connection = ds.getConnection()) {
38 SqlRunner exec = new SqlRunner(connection);
39 Map<String, Object> row = exec.selectOne("SELECT * FROM PRODUCT WHERE PRODUCTID = ?", "FI-SW-01");
40 assertEquals("FI-SW-01", row.get("PRODUCTID"));
41 }
42 }
43
44 @Test
45 void shouldSelectList() throws Exception {
46 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
47 runScript(ds, JPETSTORE_DDL);
48 runScript(ds, JPETSTORE_DATA);
49 try (Connection connection = ds.getConnection()) {
50 SqlRunner exec = new SqlRunner(connection);
51 List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM PRODUCT");
52 assertEquals(16, rows.size());
53 }
54 }
55
56 @Test
57 void shouldInsert() throws Exception {
58 DataSource ds = createUnpooledDataSource(BLOG_PROPERTIES);
59 runScript(ds, BLOG_DDL);
60 try (Connection connection = ds.getConnection()) {
61 SqlRunner exec = new SqlRunner(connection);
62 exec.setUseGeneratedKeySupport(true);
63 int id = exec.insert("INSERT INTO author (username, password, email, bio) VALUES (?,?,?,?)", "someone", "******",
64 "someone@apache.org", Null.LONGVARCHAR);
65 Map<String, Object> row = exec.selectOne("SELECT * FROM author WHERE username = ?", "someone");
66 connection.rollback();
67 assertTrue(SqlRunner.NO_GENERATED_KEY != id);
68 assertEquals("someone", row.get("USERNAME"));
69 }
70 }
71
72 @Test
73 void shouldUpdateCategory() throws Exception {
74 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
75 runScript(ds, JPETSTORE_DDL);
76 runScript(ds, JPETSTORE_DATA);
77 try (Connection connection = ds.getConnection()) {
78 SqlRunner exec = new SqlRunner(connection);
79 int count = exec.update("update product set category = ? where productid = ?", "DOGS", "FI-SW-01");
80 Map<String, Object> row = exec.selectOne("SELECT * FROM PRODUCT WHERE PRODUCTID = ?", "FI-SW-01");
81 assertEquals("DOGS", row.get("CATEGORY"));
82 assertEquals(1, count);
83 }
84 }
85
86 @Test
87 void shouldDeleteOne() throws Exception {
88 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
89 runScript(ds, JPETSTORE_DDL);
90 runScript(ds, JPETSTORE_DATA);
91 try (Connection connection = ds.getConnection()) {
92 SqlRunner exec = new SqlRunner(connection);
93 int count = exec.delete("delete from item");
94 List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM ITEM");
95 assertEquals(28, count);
96 assertEquals(0, rows.size());
97 }
98 }
99
100 @Test
101 void shouldDemonstrateDDLThroughRunMethod() throws Exception {
102 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
103 try (Connection connection = ds.getConnection()) {
104 SqlRunner exec = new SqlRunner(connection);
105 exec.run("CREATE TABLE BLAH(ID INTEGER)");
106 exec.run("insert into BLAH values (1)");
107 List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM BLAH");
108 exec.run("DROP TABLE BLAH");
109 assertEquals(1, rows.size());
110 }
111 }
112 }