package com.me.dbComponent;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.*
;
8 import java.util.function.Consumer;
9 import java.util.function.Function;
10
11 /**
12 * Created by zgj on 2017/7/27.
13 */
14 public abstract class DbTemplate {
15
16 private <T> T execute(Function<Connection, T>
function) {
17 Connection connection =
getConnection();
18 try {
19 return function.apply(connection);
20 }
finally {
21 releaseConnection(connection);
22 }
23 }
24
25 protected void releaseConnection(Connection connection) {
26 throw new RuntimeException("the method releaseConnection not implements"
);
27 }
28
29 protected Connection getConnection() {
30 throw new RuntimeException("the method getConnection not implements"
);
31 }
32
33
34 public int insert(String sql, Object... args) {
35 return insert(sql, preparedStatement ->
setParameter(preparedStatement, args));
36 }
37
38 public int insert(String sql, Iterable<?>
args) {
39 return insert(sql, preparedStatement ->
setParameter(preparedStatement, args));
40 }
41
42 public int insert(String sql, Map<String, Object>
args) {
43 return insert(sql, preparedStatement ->
setParameter(preparedStatement, args));
44 }
45
46 private int insert(String sql, Consumer<PreparedStatement>
statementSetter) {
47 return execute(connection ->
{
48 try (PreparedStatement pst =
connection.prepareStatement(sql)) {
49 connection.setAutoCommit(
false);
50 if (statementSetter !=
null) {
51 statementSetter.accept(pst);
52 }
53 int count =
pst.executeUpdate();
54 connection.commit();
55 return count;
56 }
catch (SQLException e) {
57 try {
58 connection.rollback();
59 }
catch (SQLException e1) {
60 throw new RuntimeException("transaction rollback occurred exception"
, e);
61 }
62 throw new RuntimeException(e);
63 }
64 });
65
66 }
67
68 public <T> T queryObject(String sql , Function<ResultSet, T>
resultSet, Object... args) {
69 return (T)query(sql, preparedStatement ->
setParameter(preparedStatement, args), resultSet);
70 }
71
72 private <T> T query(String sql, Consumer<PreparedStatement> consumer, Function<ResultSet, T>
resultSetExtractor) {
73 return execute(connection ->
{
74 try (PreparedStatement pst =
connection.prepareStatement(sql)) {
75 if (consumer !=
null) {
76 consumer.accept(pst);
77 }
78 try (ResultSet resultSet =
pst.executeQuery()) {
79 return resultSetExtractor.apply(resultSet);
80 }
81 }
catch (SQLException e) {
82 throw new RuntimeException(e);
83 }
84 });
85
86 }
87
88 public <T>
T queryObject(String sql, Object... args) {
89 return (T)query(sql, preparedStatement ->
setParameter(preparedStatement, args), DbTemplate::toOne);
90 }
91
92 private <T> List<T> queryList(String sql, Consumer<PreparedStatement> statementConsumer, Function<ResultSet, T>
rowMapper) {
93 return query(sql, statementConsumer, resultSet ->
{
94 try {
95 int size =
getRowSize(resultSet);
96 if (size == 0
)
97 return Collections.<T>
emptyList();
98 List<T> list =
new ArrayList<>
();
99 while (resultSet.next()) {
100 T t =
rowMapper.apply(resultSet);
101 if (!
Objects.isNull(t)) {
102 list.add(t);
103 }
104 }
105 return list;
106 }
catch (SQLException e) {
107 throw new RuntimeException(e);
108 }
109 });
110 }
111
112 public <T> List<T> queryList(String sql, Object[] args, Function<ResultSet, T>
rowMapper) {
113 return queryList(sql, preparedStatement ->
setParameter(preparedStatement, args), rowMapper);
114 }
115
116
117 public long queryCount(String sql) {
118 return queryCount(sql, ""
);
119 }
120
121 public long queryCount(String sql, Object... args) {
122 Number number =
queryObject(sql,args);
123 return number ==
null ? 0L
: number.longValue();
124 }
125
126 public boolean isExist(String sql) {
127 return isExist(sql, ""
);
128 }
129
130 public boolean isExist(String sql, Object... args) {
131 long count =
queryCount(sql, args);
132 return count > 0L
;
133 }
134
135 private static Object toOne(ResultSet resultSet) {
136 if (getRowSize(resultSet) > 1
) {
137 throw new RuntimeException("the result set is more than 1 row"
);
138 }
139 try {
140 int colAmount =
resultSet.getMetaData().getColumnCount();
141 if (colAmount == 1 &&
resultSet.next()) {
142 return resultSet.getObject(1
);
143 }
else if (colAmount > 1 &&
resultSet.next()) {
144 Object[] temp =
new Object[colAmount];
145 for (
int i = 0; i < colAmount; i++
) {
146 temp[i] = resultSet.getObject(i + 1
);
147 }
148 return temp;
149 }
150 return null;
151 }
catch (SQLException e) {
152 throw new RuntimeException(e);
153 }
154 }
155
156 private static int getRowSize(ResultSet resultSet) {
157 int rows = 0
;
158 try {
159 if (resultSet.last()) {
160 rows =
resultSet.getRow();
161 resultSet.beforeFirst();
162 }
163 return rows;
164 }
catch (SQLException e) {
165 throw new RuntimeException(e);
166 }
167 }
168
169
170 private static void setParameter(PreparedStatement preparedStatement, Object[] args) {
171 if (args ==
null || args.length == 0
) {
172 return;
173 }
174 try {
175 for (
int i = 0; i < args.length; i++
) {
176 preparedStatement.setObject(i + 1
, args[i]);
177 }
178 }
catch (SQLException e) {
179 throw new RuntimeException(e);
180 }
181
182 }
183
184 private static void setParameter(PreparedStatement preparedStatement, Iterable<?>
args) {
185 if (args ==
null) {
186 return;
187 }
188 int index = 0
;
189 try {
190 for (Iterator i =
args.iterator(); i.hasNext(); ) {
191 preparedStatement.setObject(++
index, i.next());
192 }
193 }
catch (SQLException e) {
194 throw new RuntimeException(e);
195 }
196
197 }
198
199 private static void setParameter(PreparedStatement preparedStatement, Map<String, Object>
map) {
200 if (map ==
null) {
201 return;
202 }
203 int index = 0
;
204 try {
205 for (String key : map.keySet()) {
206 preparedStatement.setObject(++
index, map.get(key));
207 }
208 }
catch (SQLException e) {
209 throw new RuntimeException(e);
210 }
211
212 }
213
214 }
DbTemplate
标签：extractor key red obj iterator final package dex ber
