|
1
33 package smallsql.junit;
34
35 import java.sql.*;
36
37
41 public class TestDeleteUpdate extends BasicTestCase {
42
43
46 public TestDeleteUpdate() {
47 super();
48 }
49
50
53 public TestDeleteUpdate(String  name) {
54 super(name);
55 }
56
57
58 public void testDelete() throws Exception {
59 Connection con = AllTests.getConnection();
60 dropTable(con,"testDelete");
61 Statement st = con.createStatement();
62 st.execute("create table testDelete(a int default 15)");
63 for(int i=0; i<10; i++){
64 st.execute("Insert into testDelete Values("+i+")");
65 }
66 assertRowCount( 10, "Select * from testDelete");
67
68 st.execute("delete from testDelete Where a=3");
69 assertRowCount( 9, "Select * from testDelete");
70
71 st.execute("delete from testDelete Where a<5");
72 assertRowCount( 5, "Select * from testDelete");
73
74 st.execute("delete from testDelete");
75 assertRowCount( 0, "Select * from testDelete");
76 dropTable(con,"testDelete");
77 }
78
79 public void testUpdate1() throws Exception {
80 Connection con = AllTests.getConnection();
81 dropTable(con,"testUpdate");
82 Statement st = con.createStatement();
83 st.execute("create table testUpdate(id int default 15, value int)");
84 for(int i=0; i<10; i++){
85 st.execute("Insert into testUpdate Values("+i+','+i+")");
86 }
87 assertRowCount( 10, "Select * from testUpdate");
88
89 int updateCount;
90 updateCount = st.executeUpdate("update testUpdate set value=103 Where id=3");
91 assertEqualsRsValue( new Integer (103), "Select value from testUpdate Where id=3");
92 assertRowCount( 10, "Select value from testUpdate");
93 assertEquals( 1, updateCount);
94
95 updateCount = st.executeUpdate("update testUpdate set value=104 Where id=3");
96 assertEqualsRsValue( new Integer (104), "Select value from testUpdate Where id=3");
97 assertRowCount( 10, "Select value from testUpdate");
98 assertEquals( 1, updateCount);
99
100 updateCount = st.executeUpdate("delete from testUpdate Where id=3");
101 assertRowCount( 9, "Select * from testUpdate");
102 assertEquals( 1, updateCount);
103
104 updateCount = st.executeUpdate("update testUpdate set value=27 Where id<5");
105 assertEquals( 4, updateCount);
106
107 dropTable(con,"testUpdate");
108 }
109
110 public void testUpdate2() throws Exception {
111 Connection con = AllTests.getConnection();
112 dropTable(con,"testUpdate");
113 Statement st = con.createStatement();
114 st.execute("create table testUpdate(id int default 15, value1 varchar(100), value2 int)");
115 for(int i=0; i<10; i++){
116 st.execute("Insert into testUpdate Values("+i+','+(i*100)+','+i+")");
117 }
118 assertRowCount( 10, "Select * from testUpdate");
119
120 st.execute("update testUpdate set value1=13 Where id=3");
121 assertEqualsRsValue( "13", "Select value1 from testUpdate Where id=3");
122 assertRowCount( 10, "Select * from testUpdate");
123
124 st.execute("update testUpdate set value1=1040 Where id=3");
125 assertEqualsRsValue( "1040", "Select value1 from testUpdate Where id=3");
126 assertRowCount( 10, "Select * from testUpdate");
127
128 st.execute("update testUpdate set value1=10400 Where id=3");
129 assertEqualsRsValue( "10400", "Select value1 from testUpdate Where id=3");
130 assertRowCount( 10, "Select * from testUpdate");
131
132 st.execute("update testUpdate set value1=13,id=3 Where id=3");
133 assertEqualsRsValue( "13", "Select value1 from testUpdate Where id=3");
134 assertRowCount( 10, "Select * from testUpdate");
135
136 st.execute("delete from testUpdate Where id=3");
137 assertRowCount( 9, "Select * from testUpdate");
138
139 dropTable(con,"testUpdate");
140 }
141
142
143 public void testUpdateMultiTables() throws Exception {
144 Connection con = AllTests.getConnection();
145 dropTable(con,"testUpdate1");
146 dropTable(con,"testUpdate2");
147 Statement st = con.createStatement();
148 st.execute("create table testUpdate1(id1 int, value1 varchar(100))");
149 st.execute("create table testUpdate2(id2 int, value2 varchar(100))");
150 st.execute("Insert into testUpdate1 Values(11, 'qwert1')");
151 st.execute("Insert into testUpdate2 Values(11, 'qwert2')");
152
153 st.execute("update testUpdate1 inner join testUpdate2 on id1=id2 Set value1=value1+'update', value2=value2+'update'");
154 ResultSet rs = st.executeQuery("Select * From testUpdate1 inner join testUpdate2 on id1=id2");
155 assertTrue( rs.next() );
156 assertEquals( "qwert1update", rs.getString("value1"));
157 assertEquals( "qwert2update", rs.getString("value2"));
158
159 dropTable(con,"testUpdate1");
160 dropTable(con,"testUpdate2");
161 }
162 }
163 |
Popular Tags |
Java API By Example, From Geeks To Geeks.