1、建立数据库
DROP TABLE IF EXISTS `TWORKERSINFO`;
CREATE TABLE `TWORKERSINFO` (
`VC_id` int(11) NOT NULL AUTO_INCREMENT,
`VC_workername` varchar(20) NOT NULL,
`VC_sex` enum('F','M'),
`VC_salary` int(11) DEFAULT '0',
`VC_email` varchar(30),
`VC_employedDates` date,
`VC_department` varchar(30),
PRIMARY KEY (`VC_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
这里ENGINE=MyISAM 说明:
MyISAM:,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的 顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚,不具有原子性。如果执行大量 的SELECT,MyISAM是更好的选择。
2、注解
package com.liyang.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* 标识表
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE})
public @interface Table {
public abstract String name() default "" ;
}
package com.liyang.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* 标识主键
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.FIELD})
public @interface Primary{
}
package com.liyang.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* 标识字段
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.FIELD})
public @interface Column {
public abstract String name() default "" ;
}
3、数据库
package com.liyang.database;
public interface DBRunner {
public abstract void execute(String sql , Object... values) throws Exception ;
public abstract void insert(Object obj) throws Exception ;
}
package com.liyang.database;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.liyang.annotation.Column;
import com.liyang.annotation.Table;
public abstract class AbstractDBRunner implements DBRunner{
@Override
public void insert(Object obj) throws Exception{
if(obj == null)
throw new Exception("插入数据为空!") ;
Class<?> clazz = obj.getClass() ;
Table table = clazz.getAnnotation(Table.class) ;
if(table == null)
throw new Exception("插入数据所指向的表为空!") ;
Field[] fields = clazz.getDeclaredFields() ;
if(fields.length == 0)
throw new Exception("插入数据所指向的列为空!") ;
String tableName = table.name() ;
List<Object> lisobj = new ArrayList<Object>() ;
List<String> liscolumns = new ArrayList<String>() ;
try{
for(Field field : fields){
field.setAccessible(true) ;
Object value = field.get(obj) ;
if(value == null) continue ;
Column column = field.getAnnotation(Column.class) ;
lisobj.add(value) ;
liscolumns.add(column.name()) ;
}
}
catch(Exception e){
throw new Exception("转换出错") ;
}
if(liscolumns.size() == 0) return ;
String sqlStr = "insert into " + tableName + "(" ;
sqlStr += liscolumns.get(0) ;
for(int i = 1 , n = liscolumns.size() ; i < n ; i++){
sqlStr += "," + liscolumns.get(i) ;
}
sqlStr += ") values( ? " ;
for(int i = 1 , n = lisobj.size() ; i < n ; i++){
sqlStr += ",?" ;
}
sqlStr += ")" ;
execute(sqlStr, lisobj.toArray()) ;
}
@SuppressWarnings("unchecked")
protected <T> T extract(Class<T> clazz, ResultSet rs) {
T t = null;
try {
ResultSetMetaData x = rs.getMetaData();
int columnCount = x.getColumnCount();
if (columnCount == 0)
return null;
Table entity = clazz.getAnnotation(Table.class);
if (entity == null) {
if (clazz.equals(Map.class)) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
map.put(x.getColumnLabel(i), rs.getObject(i));
}
return (T) map;
} else if (clazz.equals(List.class)) {
List<Object> list = new ArrayList<Object>();
for (int i = 1; i <= columnCount; i++) {
list.add(rs.getObject(i));
}
return (T) list;
} else if (clazz.equals(Object[].class)) {
Object[] os = new Object[columnCount];
for (int i = 1; i <= columnCount; i++) {
os[i - 1] = rs.getObject(i);
}
return (T) os;
} else if (columnCount == 1) {
Object value = null;
if (clazz.equals(int.class))
value = rs.getInt(1);
else if (clazz.equals(double.class))
value = rs.getDouble(1);
else if (clazz.equals(float.class))
value = rs.getFloat(1);
else if (clazz.equals(boolean.class))
value = rs.getBoolean(1);
else if (clazz.equals(long.class))
value = rs.getLong(1);
else if (clazz.equals(short.class))
value = rs.getShort(1);
else if (clazz.equals(byte.class))
value = rs.getByte(1);
else if (clazz.equals(char.class)) {
String s = rs.getString(1);
if (s.length() == 1)
value = s.charAt(0);
} else if (clazz.equals(Date.class))
value = rs.getTimestamp(1);
else if (clazz.equals(String.class))
value = rs.getString(1);
else if (clazz.equals(BigDecimal.class))
value = rs.getBigDecimal(1);
else
value = rs.getObject(1);
return (T) value;
}
return null;
}
Field[] fields = clazz.getDeclaredFields();
if (fields.length == 0)
return null;
Map<String, Field> columnDefinition = new HashMap<String, Field>();
for (Field field : fields) {
if (Modifier.isFinal(field.getModifiers())
|| Modifier.isStatic(field.getModifiers()) )
continue;
Column column = field.getAnnotation(Column.class);
columnDefinition.put(column.name().toLowerCase() , field);
}
if (columnDefinition.size() == 0)
return null;
t = clazz.newInstance();
for (int i = 1; i <= columnCount; i++){
Field field = columnDefinition.get(x.getColumnName(i).toLowerCase());
if (field != null) {
field.setAccessible(true);
Class<?> type = field.getType();
if (type.equals(int.class))
field.setInt(t, rs.getInt(i));
else if (type.equals(double.class))
field.setDouble(t, rs.getDouble(i));
else if (type.equals(float.class))
field.setFloat(t, rs.getFloat(i));
else if (type.equals(boolean.class))
field.setBoolean(t, rs.getBoolean(i));
else if (type.equals(long.class))
field.setLong(t, rs.getLong(i));
else if (type.equals(short.class))
field.setShort(t, rs.getShort(i));
else if (type.equals(byte.class))
field.setByte(t, rs.getByte(i));
else if (type.equals(char.class)) {
String s = rs.getString(i);
if (s.length() == 1)
field.setChar(t, s.charAt(0));
} else if (type.equals(Date.class))
field.set(t, rs.getTimestamp(i));
else if (type.equals(String.class))
field.set(t, rs.getString(i));
else if (type.equals(BigDecimal.class))
field.set(t, rs.getBigDecimal(i));
else
field.set(t, rs.getObject(i));
}
}
} catch (Exception e) {
e.printStackTrace() ;
}
return t;
}
}
package com.liyang.database;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
@Service
public class SpringJdbcDBRunner extends AbstractDBRunner{
@Autowired
private JdbcTemplate jdbcTemplate ;
@Override
public void execute(String sql, Object... values) throws Exception {
if(sql == null || sql.trim().equals(""))
throw new Exception("sql为空") ;
/*--输出测试--*/
/*
System.out.println(sql) ;
for(Object e : values) System.out.println(e.toString());
*/
jdbcTemplate.update(sql , values) ;
}
@Override
public <T> List<T> find(final Class<T> clazz, String sql, Object... values) throws Exception {
if(clazz == null)
throw new Exception("Class为空!") ;
if(sql == null || sql.trim().equals(""))
throw new Exception("sql为空!") ;
return jdbcTemplate.query(sql , values , new RowMapper<T>(){
@Override
public T mapRow(ResultSet rs, int arg1) throws SQLException {
return extract(clazz, rs) ;
}
}
) ;
}
}
4、实体
package com.liyang.table;
import com.liyang.annotation.Table ;
import com.liyang.annotation.Primary ;
import com.liyang.annotation.Column ;
@Table(name = "TWORKERSINFO")
public class Worker {
@Primary
@Column(name = "VC_id")
private String id ;
@Column(name = "VC_workername")
private String workername ;
@Column(name = "VC_sex")
private String sex ;
@Column(name = "VC_salary")
private String salary ;
@Column(name = "VC_email")
private String email ;
@Column(name = "VC_employedDates")
private String employedDates ;
@Column(name = "VC_department")
private String department ;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getWorkername() {
return workername;
}
public void setWorkername(String workername) {
this.workername = workername;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmployedDates() {
return employedDates;
}
public void setEmployedDates(String employedDates) {
this.employedDates = employedDates;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
}
5、工具类
package com.liyang.utils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class DateUtils {
private static final Log logger = LogFactory.getLog(DateUtils.class) ;
public static long DiffDate(String dateA , String dateB){
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
long to = 0 ;
try {
to = df.parse(dateB).getTime();
} catch (ParseException e) {
logger.error("转换错误");
}
long from = 0 ;
try {
from = df.parse(dateA).getTime();
} catch (ParseException e) {
logger.error("转换错误");
}
long dif = (from - to) / (1000 * 60 * 60 * 24) ;
return Math.abs(dif) ;
}
public static long DiffDate(String dateA , String dateB , String form){ //"yyyyMMddHHmmss"
SimpleDateFormat df = new SimpleDateFormat(form) ;
long to = 0 ;
try {
to = df.parse(dateB).getTime();
} catch (ParseException e) {
logger.error("转换错误");
}
long from = 0 ;
try {
from = df.parse(dateA).getTime();
} catch (ParseException e) {
logger.error("转换错误");
}
long dif = (from - to) / (1000 * 60 * 60 * 24) ;
return Math.abs(dif) ;
}
public static String getNowTime(String format){
SimpleDateFormat df = new SimpleDateFormat(format) ;
return df.format(new Date()) ;
}
public static String getNowTime(){
return getNowTime("yyyyMMddHHmmss") ;
}
public static String getDate(String format){ //"yyyyMMdd"
SimpleDateFormat df = new SimpleDateFormat(format);
return df.format(new Date()) ;
}
public static String getTime(String format){ //"HHmmss"
SimpleDateFormat df = new SimpleDateFormat(format);
return df.format(new Date()) ;
}
public static String GetMillitme(){
return String.format("%03d", Integer.valueOf(getTime("SS") )) ;
}
public static String GetNextDate(String day , String format){ //"yyyyMMdd"
String nextday = null ;
Calendar cal = Calendar.getInstance();
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat(format) ;
try{
date = sdf.parse(day) ;
cal.setTime(date);
cal.add(cal.DATE, 1);
System.out.println("下一天的时间是:" + sdf.format(cal.getTime()));
nextday = sdf.format(cal.getTime()) ;
}
catch (Exception e){
logger.error("转换错误");
}
return nextday ;
}
public static String GetLastDate(String day , String format){ //"yyyyMMdd"
String nextday = null ;
Calendar cal = Calendar.getInstance();
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat(format) ;
try{
date = sdf.parse(day) ;
cal.setTime(date);
cal.add(cal.DATE, -1);
System.out.println("上一天的时间是:" + sdf.format(cal.getTime()));
nextday = sdf.format(cal.getTime()) ;
}
catch (Exception e){
logger.error("转换错误");
}
return nextday ;
}
public static void main(String[] args){
// System.out.println(DateUtils.DiffDate("20120102", "20130102"));
// System.out.println( GetMillitme() ) ;
// SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式
// System.out.println(df.format(new Date()));// new Date()为获取当前系统时间
//
// System.out.println(getDate("yyyyMMdd")) ;
//
//
// System.out.println(DateUtils.GetNextDate("20150131", "yyyyMMdd"));
// System.out.println(DateUtils.GetLastDate("20150131", "yyyyMMdd"));
System.out.println(getNowTime());
}
}
6、测试
package com.liyang.test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Service;
import com.liyang.database.SpringJdbcDBRunner;
import com.liyang.table.Worker;
import com.liyang.utils.DateUtils;
@Service
public class Test {
@Autowired
public static SpringJdbcDBRunner jdbc ;
public static void main(String[] args) throws Exception {
String[] configLocations = { "applicationContext-base.xml" , "applicationContext-resource.xml" };
ApplicationContext context = new ClassPathXmlApplicationContext(configLocations);
jdbc = context.getBean("springJdbcDBRunner" , SpringJdbcDBRunner.class) ;
//注意:首字母务必小写
Worker woker = new Worker() ;
woker.setWorkername("老李");
woker.setSex("M") ;
woker.setEmail("1653921362@qq.com");
woker.setDepartment(DateUtils.getNowTime()) ;
jdbc.insert(woker) ;
}
}
7、配置
applicationContext-base.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans default-autowire="byName"
xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">
<task:annotation-driven />
<tx:annotation-driven />
<context:component-scan base-package="com.liyang" />
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
</beans>
applicationContext-resource.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans default-autowire="byName"
xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver">
</property>
<property name="url"
value="jdbc:mysql://X.X.X.X:3306/databasename?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8">
</property>
<property name="username" value="oms"></property>
<property name="password" value="oms"></property>
<property name="maxActive" value="50"></property>
<property name="maxIdle" value="50"></property>
<property name="minIdle" value="10"></property>
<property name="maxWait" value="60000"></property>
<property name="removeAbandoned" value="true"></property>
<property name="removeAbandonedTimeout" value="180"></property>
</bean>
</beans>
8.结果