Mysql中常见的数据类型
由于通过queryForList(sql)
获取List<Map<String, Object>>
数据时,经常会遇到类型转换异常问题,所以对常用数据库类型向java类型转换进行总结。
-int
-JdbcTemplate获取时会作为Integer进行处理,需要将其转型为Integer
-decimal
-JdbcTemplate获取时会作为BigDecimal进行处理,需要将其转型为BigDecimal
-float
-JdbcTemplate获取时会作为Float进行处理,需要将其转型为Float
-date,datetime
-JdbcTemplate获取时会作为Date进行处理,需要将其转型为java.util.Date,其实java.sql.Date也行
-varchar
-直接作为字符串处理,+""
即可
-tinyint
-如果数据库中tinyint的长度设为1,JdbcTemplate获取时会将其当做Boolean处理,否则当成Integer处理,同样需要转型
JdbcTemplate与Mysql快速建立连接
创建数据库资源属性文件
resources.properties
proxool.maxConnCount=10 proxool.minConnCount=5 proxool.statistics=1m,15m,1h,1d proxool.simultaneousBuildThrottle=30 proxool.trace=false db.driver.class=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/ldf?useUnicode=true&characterEncoding=utf8 db.username=root db.password=123456
创建JdbcTemplate实体配置文件
my.xml
<?xml version="1.0" encoding="UTF-8"?> <beans 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 class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:resources.properties</value> </list> </property> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy"> <property name="targetDataSource"> <bean class="org.logicalcobwebs.proxool.ProxoolDataSource"> <property name="driver" value="${db.driver.class}" /> <property name="driverUrl" value="${db.url}" /> <property name="user" value="${db.username}" /> <property name="password" value="${db.password}" /> <property name="maximumConnectionCount" value="15" /> <property name="minimumConnectionCount" value="${proxool.minConnCount}" /> <property name="statistics" value="${proxool.statistics}" /> <property name="simultaneousBuildThrottle" value="${proxool.simultaneousBuildThrottle}" /> <property name="trace" value="${proxool.trace}" /> <property name="houseKeepingTestSql" value="SELECT 1"/> <property name="houseKeepingSleepTime" value="3600000" /> <property name="testBeforeUse" value="true"/> </bean> </property> </bean> <bean id="jdbcTemplateWp" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource"> <ref bean="dataSource" /> </property> </bean> </beans>
测试数据库连接
MyTest.java
public class MyTest { public static void main(String[] args){ ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:my.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate)applicationContext.getBean("jdbcTemplateWp"); String sql = "SELECT * FROM a WHERE weight>?"; List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, new Object[]{43}); for (Map<String, Object> map : result) { int id = (Integer) map.get("id"); String name = map.get("name")+""; Date createdate = (Date)map.get("createdate"); BigDecimal avg_grade = (BigDecimal)map.get("avg_grade"); Float weight = (Float)map.get("weight"); Integer sex = (Integer)map.get("sex"); System.out.println(id+" "+name+" "+createdate+" "+avg_grade+" "+weight+" "+sex); } } }
在最近的项目中遇到一些任务比较耗时,调试JdbcTemplate比较耗时,可以通过自行快速创建数据库连接进行测试。
JdbcTemplate中的query函数
如果想以记录的形式返回结果,即
List<Map<String, Object>>
的结果集返回,可以使用queryForList(String sql, Object... args)
,在结果集中通过列名获取属性值,具体代码见MyTest.java。
数据库表字段类型如下:
运行结果如下:
如果想以实体集的形式返回,即
List<Student>
的形式,可以使用query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper)
。列名和类的属性对应关系主要靠属性名(其实是set方法名)实现的。
Student类的代码如下:Student.java
public class Student { private String name; private BigDecimal avg_grade; private Date createdate; private float weight; private int sex; public String getName() { return name; } public void setName(String name) { this.name = name; } public BigDecimal getAvg_grade() { return avg_grade; } public void setAvg_grade(BigDecimal avg_grade) { this.avg_grade = avg_grade; } public Date getCreatedate() { return createdate; } public void setCreatedate(Date createdate) { this.createdate = createdate; } public float getWeight() { return weight; } public void setWeight(float weight) { this.weight = weight; } public int getSex() { return sex; } public void setSex(int sex) { this.sex = sex; } @Override public String toString() { return "Student[name="+name+", createdate="+createdate+", avg_grade="+avg_grade+", weight=" +weight+", sex="+sex+"]"; } }
测试类如下:
MyTest.java
public class MyTest { public static void main(String[] args){ ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:my.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate)applicationContext.getBean("jdbcTemplateWp"); String sql = "SELECT * FROM a WHERE weight>?"; RowMapper rm = new BeanPropertyRowMapper(Student.class); List<Student> result = jdbcTemplate.query(sql, new Object[]{43}, rm); for (Student stu : result) { System.out.println(stu); } } }
运行结果如下:
java和数据库中的date类型
数据库中有data和datetime类型,date类型是具体到年月日,而datetime具体到时分秒,在JdbcTemplate获取时都会转型为Date类型。
通过字符串获取Date的方式:
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = null; try { date = dateFormat.parse("2017-01-03"); } catch (ParseException e) { e.printStackTrace(); } System.out.println(date);
获取当前时间的前一天(前一月,前一年)
Date today = new Date(); calendar.setTime(today); calendar.set(Calendar.DATE, calendar.get(Calendar.DATE) - 1); Date yesterday = calendar.getTime(); calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1); Date lastMonth = calendar.getTime(); calendar.set(Calendar.YEAR, calendar.get(Calendar.YEAR) - 1); Date lastYear = calendar.getTime(); System.out.println("yeaterday="+dateFormat.format(yesterday)); System.out.println("lastMonth="+dateFormat.format(lastMonth)); System.out.println("lastYear="+dateFormat.format(lastYear));
输出结果如下: