Using Namedparameterjdbctemplate to insert enum type data into Oracle

When you use Namedparameterjdbctemplate to insert objects with enumerated type data into the database, the Java and SQL type mismatch occurs, for example, for the following Java class definition and the corresponding Oracle table definition:

1 public class MetaPhysicColumn {

 
 
 2 
 3     public enum DataType{NUMBER, STRING, DATE, TIMESTAMP}
 4 
 5     private String id;
 6     private String name;
 7     private String description;
 8     private String tableId;
 9     private DataType dataType;
10     private boolean primaryKey;
11 }

 
1 create table meta_physic_column
2 (
3   id              varchar2(128),
4   name            varchar2(128),
5   description     varchar2(512),
6   table_id        varchar2(128),
7   data_type       varchar2(128),
8   primary_key     varchar2(1)
9 );

An "Invalid column type" error occurred when inserting data by executing the following code

1 public void insertPhysicColumn(MetaPhysicColumn physicColumn) {
2     String sql = "insert into meta_physic_column (id, name, description, table_id, data_type, primary_key) values( :id, :name, :description, :tableId, :dataType, :primaryKey)"；
3     SqlParameterSource paramSource = new BeanPropertySqlParameterSource(physicColumn);
4     this.getNamedParameterJdbcTemplate().update(sql, paramSource);
5 }

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into cngtest(id, name, description, table_id, data_type, primary_key) values( ?, ?, ?, ?, ?, ?)]; SQL state [ 99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

     At org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
     At org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
     At org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
     At org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)

There is no problem with reading VARCHAR2 type data from Oracle to the appropriate enumeration type, but when inserting the data, Beanpropertysqlparametersource does not provide a mapping of the enumeration type to VARCHAR2, which is an error when executed. From source analysis: Sqlparametersource is used to implement named parameter passing interface, Namedparameterjdbctemplate.update () by calling Getsqltype (Var) and GetValue (Var) Two functions to get the SQL type of the column and the bound object in Java, Beanpropertysqlparametersource is an implementation of Sqlparametersource, The inheritance structure is Beanpropertysqlparametersource extends Abstractsqlparametersource implements Sqlparametersource, The Abstractsqlparametersource class provides the Registersqltype () function to manually register the column type, which can be used to register the enumeration column as the VARCHAR2 type, with the code as follows, but the enumeration property names in each object are different. This method does not have generality;

 
1 public void insertPhysicColumn(MetaPhysicColumn physicColumn) {
2     String sql = "insert into meta_physic_column (id, name, description, table_id, data_type, primary_key)      
3                   values( :id, :name, :description, :tableId, :dataType, :primaryKey)"；
4     BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(physicColumn);
5     paramSource.registerSqlType("dataType", Types.VARCHAR);
6     this.getNamedParameterJdbcTemplate().update(sql, paramSource);
7 }

Another workaround is to inherit Beanpropertysqlparametersource, overriding the Getsqltype () method in the subclass, where the parent class's Getsqltype () is called first, and if no SQL type is found for the column, And this column corresponds to an enumeration type, it returns VARCHAR2, exactly as the subclass and Beanpropertysqlparametersource are used, but provides support for enumeration types, as shown in the code below

 
 1 public class MyBeanPropertySqlParameterSource extends BeanPropertySqlParameterSource {
 2 
 3     public MyBeanPropertySqlParameterSource(Object object) {
 4         super(object);
 5     }
 6     @Override
 7     public int getSqlType(String var) {
 8         int sqlType = super.getSqlType(var);
 9         if (sqlType == TYPE_UNKNOWN && hasValue(var)) {
10             if (getValue(var).getClass().isEnum()) {
11                 sqlType = Types.VARCHAR;
12             }
13         }
14         return sqlType;
15     }
16 }

1 public void insertPhysicColumn(MetaPhysicColumn physicColumn) {
2     String sql = "insert into meta_physic_column (id, name, description, table_id, data_type, primary_key) values( :id, :name, :description, :tableId, :dataType, :primaryKey)"；
3     SqlParameterSource paramSource = new MyBeanPropertySqlParameterSource(physicColumn);
4     this.getNamedParameterJdbcTemplate().update(sql, paramSource);
5 }

 

PS: For a Boolean type, Beanpropertysqlparametersource maps it to SQL VARCHAR2, sets the table structure to number as number, and reads the VARCHAR2 type from the database to the Boolean type , JdbcTemplate can only recognize 0 and 1, and other characters will also be error-aware. 

Using Namedparameterjdbctemplate to insert enum type data into Oracle

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch