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", "******", "someone@apache.org", Null.LONGVARCHAR);
64 Map<String, Object> row = exec.selectOne("SELECT * FROM author WHERE username = ?", "someone");
65 connection.rollback();
66 assertTrue(SqlRunner.NO_GENERATED_KEY != id);
67 assertEquals("someone", row.get("USERNAME"));
68 }
69 }
70
71 @Test
72 void shouldUpdateCategory() throws Exception {
73 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
74 runScript(ds, JPETSTORE_DDL);
75 runScript(ds, JPETSTORE_DATA);
76 try (Connection connection = ds.getConnection()) {
77 SqlRunner exec = new SqlRunner(connection);
78 int count = exec.update("update product set category = ? where productid = ?", "DOGS", "FI-SW-01");
79 Map<String, Object> row = exec.selectOne("SELECT * FROM PRODUCT WHERE PRODUCTID = ?", "FI-SW-01");
80 assertEquals("DOGS", row.get("CATEGORY"));
81 assertEquals(1, count);
82 }
83 }
84
85 @Test
86 void shouldDeleteOne() throws Exception {
87 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
88 runScript(ds, JPETSTORE_DDL);
89 runScript(ds, JPETSTORE_DATA);
90 try (Connection connection = ds.getConnection()) {
91 SqlRunner exec = new SqlRunner(connection);
92 int count = exec.delete("delete from item");
93 List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM ITEM");
94 assertEquals(28, count);
95 assertEquals(0, rows.size());
96 }
97 }
98
99 @Test
100 void shouldDemonstrateDDLThroughRunMethod() throws Exception {
101 DataSource ds = createUnpooledDataSource(JPETSTORE_PROPERTIES);
102 try (Connection connection = ds.getConnection()) {
103 SqlRunner exec = new SqlRunner(connection);
104 exec.run("CREATE TABLE BLAH(ID INTEGER)");
105 exec.run("insert into BLAH values (1)");
106 List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM BLAH");
107 exec.run("DROP TABLE BLAH");
108 assertEquals(1, rows.size());
109 }
110 }
111 }