I want to make it into a library, but I don't know what to do ... For those who have such troubles, I will explain using DAO (Data Access Object) as an example.
That is, don't suddenly think about the logic of the contents. Thinking about things from the outside. This also leads to object-oriented programming.
Specifically, I think the following should be considered from the perspective of the library user.
I would like to have a DAO (Data Access Object) theme.
First, take a look at the code below Then, let's think about what kind of constructor and method are easy to use.
DB access code
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**User master DAO*/
public class UserMstDao{
private Connection connection;
public UserMstDao(Connection connection){
this.connection = connection;
}
/**Get 1 user record*/
* @throws SQLException */
public UserMst selectUser(String userId) throws SQLException{
PreparedStatement ps = connection.prepareStatement("select * from UserMst where userId = ?");
ps.setObject(1, userId);
ResultSet rs = ps.executeQuery();
UserMst bean = null;
if (rs.next()) {
bean= new UserMst();
bean.userId = (Integer)rs.getObject("userId");
bean.userName = (String)rs.getObject("userName");
}
return bean;
}
/**Get all user records*/
* @throws SQLException */
public List<UserMst> selectUserList() throws SQLException{
PreparedStatement ps = connection.prepareStatement("select * from UserMst");
ResultSet rs = ps.executeQuery();
List<UserMst> list = new ArrayList<>();
while (rs.next()) {
UserMst bean = new UserMst();
bean.userId = (Integer)rs.getObject("userId");
bean.userName = (String)rs.getObject("userName");
list.add(bean);
}
return list;
}
/**User master*/
public static class UserMst{
//There is no getter setter because it is a cut-out implementation.
public Integer userId;
public String userName;
}
}
If you write it without using the library, it will look like this no matter who writes it. Using ResultSet # getObject instead of ResultSet # getInt Since the return value of getInt is a primitive type, there is a trap that it will be 0 if the DB value is null, so this is a prevention. For details, see below I was addicted to getXxxx of ResultSet because of primitive type (Java) --Qiita
Based on what you said in "The secret to creating a library" together Let's think about what to do with the following two points.
Probably everyone will get different answers, but don't worry about libraries like that.
First of all, I came up with this kind of code.
Jdbc jdbc = new Jdbc(connection);
List<UserMst> userList = jdbc.selectList("select * from UserMst", UserMst.class);
UserMst user = jdbc.selectOne("select * from UserMst where userId = ?", UserMst.class, 0);
If you pass only SQL and class information and SQL parameters to the method, the result will be returned! Based on the class information, create an object of that class, and if the field name and DB item name match, set it in that field! It's a lot less code than the previous code!
You can make full use of the template engine to create an external SQL file, or you can describe variables in SQL statements, The scale will be huge, so let's not go that far this time.
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
*Makes Jdbc easy to work with.
*/
public class Jdbc {
private Connection conn;
public Jdbc(Connection conn) {
this.conn = conn;
}
/**
*Set parameters to PreparedStatement.
* @param statement Prepaid statement
* @param params parameter
* @throws SQLException
*/
private void setParams(PreparedStatement statement, Object... params) throws SQLException {
int paramNo = 1;
for( Object param : params ) {
statement.setObject(paramNo++, param);
}
}
/**
*Convert from DB data to object of specified class.
*If you specify a class under java package for clazz, set only one item
* @param rs result set
* @param clazz bean class
* @return bean
* @throws IllegalAccessException
* @throws InstantiationException
* @throws SQLException
*/
private <E> E toObject(ResultSet rs, Class<E> clazz) throws InstantiationException, IllegalAccessException, SQLException {
E bean = null;
if( rs.next() ) {
Field[] fields = clazz.getFields();
bean = clazz.newInstance();
for( Field f: fields ) {
Object val = rs.getObject( f.getName() );
f.set(bean, val);
}
}
return bean;
}
/**
*Convert from DB data to a list of objects of the specified class.
* @param rs result set
* @param clazz bean class
* @return bean list
* @throws SQLException
* @throws IllegalAccessException
* @throws InstantiationException
*/
private <E> List<E> toObjectList(ResultSet rs, Class<E> clazz) throws SQLException, InstantiationException, IllegalAccessException {
List<E> rsList = new ArrayList<>();
while (rs.next()) {
Field[] fields = clazz.getFields();
E bean = clazz.newInstance();
for( Field f: fields ) {
Object val = rs.getObject( f.getName() );
f.set(bean, val);
}
rsList.add(bean);
}
return rsList;
}
/**
*SQL error handling
* @param e exception
* @param sql SQL statement
* @return exception
*/
private SQLException sqlErr(Throwable e, CharSequence sql) {
return new SQLException("sql error!\nerror occurred sql="+sql, e);
}
/**
*Execute insert, update, etc..
* @param sql
* @param params
* @return
* @throws SQLException
*/
public int update(String sql, Object...params) throws SQLException {
try (PreparedStatement statement = conn.prepareStatement(sql.toString())){
setParams(statement,
params);
return statement.executeUpdate();
}catch (SQLException e){
throw sqlErr(e, sql);
}
}
/**
*Get only 1 record or 1 column.
* @param sql
* @param clazz
* @param params
* @return
* @throws SQLException
*/
public <E> E selectOne(String sql, Class<E> clazz, Object...params) throws SQLException {
ResultSet rs = null;
try (PreparedStatement statement = conn.prepareStatement(sql.toString())){
setParams(statement,
params);
rs = statement.executeQuery();
E val = toObject(rs, clazz);
return val;
}catch(SQLException | InstantiationException | IllegalAccessException e) {
throw sqlErr(e, sql);
}finally {
try {
if( rs != null ) {
rs.close();
}
} catch (SQLException e) {
throw sqlErr(e, sql);
}
}
}
/**
*Get multiple records.
* @param sql
* @param clazz
* @param params
* @return
* @throws SQLException
*/
public <E> List<E> selectList(String sql, Class<E> clazz, Object...params) throws SQLException {
ResultSet rs = null;
try (PreparedStatement statement = conn.prepareStatement(sql.toString())){
setParams(statement,
params);
rs = statement.executeQuery();
List<E> rsList = toObjectList(rs, clazz);
return rsList;
}catch(SQLException | InstantiationException | IllegalAccessException e) {
throw sqlErr(e, sql);
}finally {
try {
if( rs != null ) {
rs.close();
}
} catch (SQLException e) {
throw sqlErr(e, sql);
}
}
}
}
The point of this code is
You are getting the array of java.lang.reflect.Field from Class and turning it.
This is called reflection.
Used in many FW / libraries.
Recommended Posts