There are many Java sources out there that have no choice but to realize legacy JDBC connections. It's one of the programmer's worries. If it's just maintenance, you may have to make a new one.
As a case that comes to mind immediately in the sense of legacy It is in the form of "mapping the information pulled from DB by Select as a bean" The processing process is ** still cool ** slack ...
"Keep query results once in ResultSet" "Turn the cursor of ResultSet to fill the bean" "Implement business logic by looping beans" It's like that.
The design and definition of SQL and Bean are correct, but in the process It is often the case that the whole thing gets stuck just by making a careless mistake.
More intuitively, ** Isn't it complete with just SQL and Bean? ** This is the article.
As the title says. Strictly speaking, the "returned bean type" must be specified in advance. Is it close to ** My Bottle of Starbucks **? When you place an order (SQL) and give my bottle (Bean) to the clerk My bottle is filled with coffee and returns.
You can see it by looking at the code that comes out after this. There is a part that ** completely ignores ** "type safety", which is one of the major features of Java. Personally, it's "separate", but people who get caught may get caught. Please forgive me.
[ORM](https://ja.wikipedia.org/wiki/%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83 % 88% E9% 96% A2% E4% BF% 82% E3% 83% 9E% E3% 83% 83% E3% 83% 94% E3% 83% B3% E3% 82% B0) is not detailed at all. So with better functionality with existing middleware and libraries There are likely to be resources that do what I want to do, There is also a closeout that there are many negative opinions rather than pros and cons of ORM itself. Although it is premature, I have stopped forcibly thrusting my neck. (Personally, ORM recognizes it as a ** broad ** word, so I don't think it's all bad It is a fact that I hate word hunting, so I avoided it.)
The following article is the closest to what I want to do. The source of the base part is diverted almost as it is. [Java] Let's make a DB access library!
Java 6 or later
DB connection infrastructure class and class that controls mapping.
A class that manages placeholders that are parameters to SQL. This is a ** type danger ** that is not type safe.
The SQL statement you want to execute. Regardless of CRUD, there is no need to make it persistent as a file or DB record.
Bean class corresponding to the extraction column of Select. Create one for each Select statement.
I/O The I / O required for the minimum operation is as follows.
-Full path of JDBC driver class (oracle.jdbc.driver.OracleDriver, etc.) -JDBC connector (jdbc: oracle: thin: @ // localhost: 1521 / xe, etc.) -JDBC connection user -JDBC connection password ・ SQL -SQL parameters (however, not required if placeholders are not used)
Bean
It is based on the JDBCManager and SqlParameter classes.
JDBCManager class
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class JDBCManager {
private Connection conn;
@SuppressWarnings("unused")
private JDBCManager() throws Throwable{}
public JDBCManager(String driver,String url,String user,String password) throws ClassNotFoundException,SQLException{
Class.forName(driver);
this.conn = DriverManager.getConnection(url, user, password);
this.conn.setAutoCommit(false);
}
private void setParams(PreparedStatement statement, SqlParameter sqlParameter) throws SQLException {
if(sqlParameter == null || !sqlParameter.hasParameters()) {
return;
}
int paramNo = 1;
for( Object param : sqlParameter.toParameter() ) {
statement.setObject(paramNo++, param);
}
}
public void commit() throws SQLException{
this.conn.commit();
}
public void rollback() throws SQLException{
this.conn.rollback();
}
public void close() throws SQLException{
this.conn.close();
}
private <E> E toObject(ResultSet rs, Class<E> clazz) throws InstantiationException, IllegalAccessException, SQLException {
E bean = null;
if( rs.next() ) {
Field[] fields = clazz.getDeclaredFields();
bean = clazz.newInstance();
for (Field f : fields) {
f.setAccessible(true);
Object val = rs.getObject(f.getName());
f.set(bean, val);
}
}
return bean;
}
private <E> List<E> toObjectList(ResultSet rs, Class<E> clazz) throws SQLException, InstantiationException, IllegalAccessException {
List<E> rsList = new ArrayList<E>();
while (rs.next()) {
Field[] fields = clazz.getDeclaredFields();
E bean = clazz.newInstance();
for( Field f: fields ) {
f.setAccessible(true);
Object val = rs.getObject( f.getName() );
f.set(bean, val);
}
rsList.add(bean);
}
return rsList;
}
private SQLException sqlErr(Throwable e, CharSequence sql) {
return new SQLException("sql error!\nerror occurred sql="+sql, e);
}
public int insert(String sql, SqlParameter sqlParameter) throws SQLException {
return exert(sql,sqlParameter);
}
public int update(String sql, SqlParameter sqlParameter) throws SQLException {
return exert(sql,sqlParameter);
}
public int delete(String sql, SqlParameter sqlParameter) throws SQLException {
return exert(sql,sqlParameter);
}
private int exert(String sql, SqlParameter sqlParameter) throws SQLException {
PreparedStatement statement = null;
try {
statement = conn.prepareStatement(sql.toString());
setParams(statement,sqlParameter);
int ret = statement.executeUpdate();
return ret;
}catch (SQLException e){
throw sqlErr(e, sql);
}finally {
try {
if(statement != null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public <E> E selectOne(String sql, Class<E> clazz) throws SQLException {
return selectOne(sql,clazz,null);
}
public <E> E selectOne(String sql, Class<E> clazz, SqlParameter sqlParameter) throws SQLException {
ResultSet rs = null;
PreparedStatement statement = null;
try{
statement = conn.prepareStatement(sql.toString());
setParams(statement,sqlParameter);
rs = statement.executeQuery();
E val = toObject(rs, clazz);
return val;
}catch(Exception e) {
throw sqlErr(e, sql);
}finally {
try {
if( rs != null ) {
rs.close();
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
try {
if(statement != null){
statement.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
public <E> List<E> selectList(String sql, Class<E> clazz) throws SQLException {
return selectList(sql,clazz,null);
}
public <E> List<E> selectList(String sql, Class<E> clazz, SqlParameter sqlParameter) throws SQLException {
ResultSet rs = null;
PreparedStatement statement = null;
try {
statement = conn.prepareStatement(sql.toString());
setParams(statement,sqlParameter);
rs = statement.executeQuery();
List<E> rsList = toObjectList(rs, clazz);
return rsList;
}catch(Exception e) {
throw sqlErr(e, sql);
}finally {
try {
if( rs != null ) {
rs.close();
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
try {
if(statement != null){
statement.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
}
SqlParameter class
import java.util.ArrayList;
import java.util.List;
public class SqlParameter {
@SuppressWarnings("rawtypes")
private List list = null;
@SuppressWarnings("rawtypes")
public SqlParameter(int paramNumber){
list = new ArrayList(paramNumber);
}
@SuppressWarnings("rawtypes")
public SqlParameter(){
list = new ArrayList();
}
@SuppressWarnings("unchecked")
public <E> void addParameter(E e){
list.add(e);
}
public boolean hasParameters() {
return list.size()==0 ? false : true;
}
public Object[] toParameter(){
return list.toArray();
}
}
I will move it in four cases. The target DB is "Oracle (Express Edition)". The operation targets are the "EMP" and "DEPT" tables that exist by default in the schema.
(1) Select: Multiple acquisition SQL without WHERE clause (no placeholder required) (2) Select: Multiple acquisition SQL with WHERE clause (with placeholder) ③ Select: Single acquisition SQL ④ Update (insert, update, delete)
We will create beans according to each SQL. The point is to make the Select alias (AS) and the Bean field name the same **.
Also, declare the bean field type in the class according to the JDBC specification.
In Select of this sample, we are operating on a single table. The same processing can be performed with Select for multiple tables using ** join **.
The point is "** SQL and Bean should match **".
Select_Multiple acquisition SQL without WHERE clause (no placeholder required)
SELECT
--VARCHAR2
DNAME AS dname
FROM
DEPT
DeptBean class
public class DeptBean {
private String dname = null;
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
}
Select_Get multiple records with WHERE clause (with placeholder) SQL
SELECT
--NUMBER
EMPNO AS empNo,
--VARCHAR2
ENAME AS ename,
--DATE
HIREDATE AS hireDate
FROM
EMP
WHERE
DEPTNO = ?
AND
JOB = ?
EmpBean class
import java.math.BigDecimal;
import java.sql.Timestamp;
public class EmpBean {
private BigDecimal empNo = null;
private String ename = null;
private Timestamp hireDate = null;
public BigDecimal getEmpNo() {
return empNo;
}
public void setEmpNo(BigDecimal empNo) {
this.empNo = empNo;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Timestamp getHireDate() {
return hireDate;
}
public void setHireDate(Timestamp hireDate) {
this.hireDate = hireDate;
}
}
Select_Single capture SQL
SELECT
--NUMBER
EMPNO AS empNo,
--VARCHAR2
ENAME AS ename,
--DATE
HIREDATE AS hireDate
FROM
EMP
WHERE
//PK
EMPNO = ?
EmpBean class
(Since it is the same as above, it is omitted)
Let's use insert as a sample. Since the result is received as an int, you do not need to create a bean for updating.
insert
INSERT INTO DEPT
--NUMBER,VARCHAR2,VARCHAR2
(DEPTNO, DNAME, LOC)
values
(?, ?, ?)
I will move all the above four cases.
Test class
import java.math.BigDecimal;
import java.util.List;
public class JDBCTest {
public static void main(String[] args) throws Throwable{
JDBCTest t = new JDBCTest();
t.execute();
}
public void execute() throws Throwable{
//JDBC connection information
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@//localhost:1521/xe";
String user = "ouser";
String password = "ouser";
//Initialize JDBC Manager
JDBCManager jdbc = new JDBCManager(driver,url,user,password);
//Pattern 1. Multiple acquisition SQL without WHERE clause (no placeholder required)
String sql = "SELECT "
+ "DNAME AS dname "
+ "FROM "
+ "DEPT ";
//Get List
List<DeptBean> dlist = jdbc.selectList(sql, DeptBean.class);
System.out.println("――― Pattern 1 ―――");
for(DeptBean d : dlist) {
System.out.println(d.getDname());
}
//Pattern 2. Get multiple records with WHERE clause (with placeholder) SQL
//Fit to bean field with alias
sql = "SELECT "
+ "EMPNO AS empNo, "
+ "ENAME AS ename, "
+ "HIREDATE AS hireDate "
+ "FROM "
+ "EMP "
+ "WHERE "
+ "DEPTNO = ? "
+ "AND "
+ "JOB = ? ";
//Two placeholders
SqlParameter p = new SqlParameter(2);
//Bind in order from the beginning
p.addParameter(new BigDecimal(30));
p.addParameter("SALESMAN");
//Get List
List<EmpBean> elist = jdbc.selectList(sql, EmpBean.class, p);
System.out.println("――― Pattern 2 ―――");
for(EmpBean e : elist) {
System.out.println(e.getEmpNo() + "\t" + e.getEname() + "\t" + e.getHireDate());
}
//Pattern 3. Single capture SQL
sql = "SELECT "
+ "EMPNO AS empNo, "
+ "ENAME AS ename, "
+ "HIREDATE AS hireDate "
+ "FROM "
+ "EMP "
+ "WHERE "
//PK
+ "EMPNO = ? ";
p = new SqlParameter(1);
p.addParameter(new BigDecimal(7369));
//Get Bean
EmpBean emp = jdbc.selectOne(sql, EmpBean.class, p);
System.out.println("――― Pattern 3 ―――");
System.out.println(emp.getEname());
//Pattern 4. Update (insert,update,delete）
sql = "INSERT INTO DEPT "
+ "(DEPTNO, DNAME, LOC) "
+ "values "
+ "(?, ?, ?) ";
p = new SqlParameter(3);
p.addParameter(new BigDecimal(99));
p.addParameter("SPECIAL");
p.addParameter("JAPAN");
int i = jdbc.insert(sql, p);
System.out.println("――― Pattern 4 ―――");
System.out.println("The number of Inserts is"+i+"It is a matter");
jdbc.commit();
//Actually error trap and jdbc with catch.rollback()Run
jdbc.close();
//Actually error trapped and finally jdbc.close()Run
}
}
The execution result is as follows.
Execution result (standard output)
――― Pattern 1 ―――
ACCOUNTING
RESEARCH
SALES
OPERATIONS
――― Pattern 2 ―――
7499 ALLEN 1981-02-20 00:00:00.0
7521 WARD 1981-02-22 00:00:00.0
7654 MARTIN 1981-09-28 00:00:00.0
7844 TURNER 1981-09-08 00:00:00.0
――― Pattern 3 ―――
SMITH
――― Pattern 4 ―――
The number of Inserts is 1.
Even if you compare from the article that I referred to There is almost no new content, The risk of causing a run-time error is increased because it is not type-safe. Personally, I handle at most three targets, ** SQL **, ** parameters **, and ** Bean **. If it's about messing up with Resultset and taking time I think it's worth the trade-off of type safety.
Also, in an environment where Java cannot be upgraded due to various reasons, In an environment where DB-based middleware and libraries such as ORM cannot be installed, Even if there are environmental and operational restrictions I like the fact that it can only run on older Java VMs **.
This time I targeted Oracle as the database If you can connect with JDBC, there are some dialectal differences in behavior. I'm optimistic that any database can be used.