608145721.png
最近工作经常会遇到要做一个很小的微服务，服务中需要查数据库，
尽量不要使用 框架。习惯了Mybatis的方便，突然还不会了，对应该只有JDBC最小了吧；于是写了一 个小小的工具类提供服务，这个类提供一个数据库connection的初始化，然后提供update，select的接口；当然为了更加方便，里面用了点java的反射，提供统一查询接口。如下：
public class MysqlDBService {
Logger LOG= LoggerFactory.getLogger(MysqlDBService.class);
private Connection connection;
public Connection getConnection(){
return connection;
}
public MysqlDBService(){
}
public MysqlDBService(String uri) {
try {
this.initStatement(uri);
} catch (Exception e) {
e.printStackTrace();
}
}
public MysqlDBService (String uri, String name, String password) throws Exception {
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
connection= DriverManager.getConnection(uri,name,password);
}
public void initConnection(String uri) throws Exception{
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
connection= DriverManager.getConnection(uri);
}
public <Target> List<Target> parseQueryList(Class<Target> destType,String sql) {
List<Target> list= new ArrayList<Target>();
Field[] fields= destType.getFields();
try{
Statement statement= connection.createStatement();
ResultSet set= statement.executeQuery(sql);
while (set.next()){
Target target= destType.newInstance();
for (Field field:fields){
field.setAccessible(true);
String typeName= field.getType().getName();
String fieldName= CamelCaseUtils.toUnderlineName(field.getName());
Object value= null;
//LOG.info("statement field is {}",fieldName);
if(typeName.equals("java.lang.String")){
value= getStrValue(set, fieldName);
}else if(typeName.equals("java.util.Date")){
value= getDateValue(set, fieldName);
}else if(typeName.equals("java.lang.Integer")){
value= getIntValue(set, fieldName);
}else if(typeName.equals("java.lang.Long")){
value= getLongValue(set, fieldName);
}else if(typeName.equals("java.math.BigDecimal")){
value= getBigDecimalValue(set, fieldName);
}else if(typeName.equals("java.sql.Timestamp")){
value= getTimestampValue(set, fieldName);
}else if(typeName.equals("java.lang.Double")){
value= getDoubleValue(set,fieldName);
}
if(value!=null){
field.set(target, value);
}
}
list.add(target);
}
}catch (Exception e){
LOG.info("parseQueryList failed for Collection {}", destType.getName());
}
return list;
}
public static String getStrValue(ResultSet set,String fieldName) throws IOException, SQLException {
try{
NClob clob= set.getNClob(fieldName);
if(clob!=null){
BufferedReader r = new BufferedReader(clob.getCharacterStream());
StringBuilder b = new StringBuilder();
String line;
while((line=r.readLine())!=null) {
b.append(line);
}
return b.toString();
}
}catch (Exception e){
return set.getString(fieldName);
}
return null;
}
public List<Integer> parseFieldList(String sql){
List<Integer> list= new ArrayList<Integer>();
try{
Statement statement= connection.createStatement();
ResultSet set= statement.executeQuery(sql);
while (set.next()){
try{
list.add(set.getInt(1));
}catch (Exception e){
e.printStackTrace();
}
}
}catch (Exception e){
e.printStackTrace();
}
return list;
}
public Integer parseOneField(String sql){
try{
Statement statement= connection.createStatement();
ResultSet set= statement.executeQuery(sql);
while (set.next()){
try{
return set.getInt(1);
}catch (Exception e){
e.printStackTrace();
}
}
}catch (Exception e){
e.printStackTrace();
}
return null;
}
public static Integer getIntValue(ResultSet set,String fieldName) throws SQLException {
return set.getInt(fieldName);
}
public static Date getDateValue(ResultSet set,String fieldName) throws SQLException {
return set.getDate(fieldName);
}
public static Long getLongValue(ResultSet set,String fieldName) throws SQLException {
return set.getLong(fieldName);
}
public static BigDecimal getBigDecimalValue(ResultSet set, String fieldName) throws SQLException {
return set.getBigDecimal(fieldName);
}
public static Double getDoubleValue(ResultSet set, String fieldName) throws SQLException {
return set.getDouble(fieldName);
}
public static Timestamp getTimestampValue(ResultSet set, String fieldName) throws SQLException {
return set.getTimestamp(fieldName);
}
public void update(String sql,Statement statement){
try {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public int update(String sql){
try {
Statement statement= connection.createStatement();
statement.executeUpdate(sql);
return 1;
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
}
上面Service还依赖于一个驼峰字段转换类，如下：
public class CamelCaseUtils {
private static final char SEPARATOR = '_';
public static String toUnderlineName(String s) {
if (s == null) {
return null;
}
StringBuilder sb = new StringBuilder();
boolean upperCase = false;
for (int i = 0; i < s.length(); i++) {
char c = s.charAt(i);
boolean nextUpperCase = true;
if (i < (s.length() - 1)) {
nextUpperCase = Character.isUpperCase(s.charAt(i + 1));
}
if ((i >= 0) && Character.isUpperCase(c)) {
if (!upperCase || !nextUpperCase) {
if (i > 0) sb.append(SEPARATOR);
}
upperCase = true;
} else {
upperCase = false;
}
sb.append(Character.toLowerCase(c));
}
return sb.toString();
}
public static String toCamelCase(String s) {
if (s == null) {
return null;
}
s = s.toLowerCase();
StringBuilder sb = new StringBuilder(s.length());
boolean upperCase = false;
for (int i = 0; i < s.length(); i++) {
char c = s.charAt(i);
if (c == SEPARATOR) {
upperCase = true;
} else if (upperCase) {
sb.append(Character.toUpperCase(c));
upperCase = false;
} else {
sb.append(c);
}
}
return sb.toString();
}
public static String toCapitalizeCamelCase(String s) {
if (s == null) {
return null;
}
s = toCamelCase(s);
return s.substring(0, 1).toUpperCase() + s.substring(1);
}
public static void main(String[] args) {
System.out.println(toUnderlineName("userAvatar"));
}
}
网友评论