SpringMVC 应用 JDBC 访问数据库

2015-11-11 11:50:53   最后更新: 2015-11-11 11:53:36   访问数量:727




JDBC 即 Java Data Base Connectivity,是 java 连接数据库的基准接口,很多 ORM 在 JDBC 的基础上进行封装,实现灵活性

 

使用 JDBC 首先需要在 pom.xml 中加入 jdbc 依赖

<properties> <java-version>1.7</java-version> <org.springframework.version>4.2.2.RELEASE</org.springframework.version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework.version}</version> <exclusions> <exclusion> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${org.springframework.version}</version> </dependency> </dependencies>

 

 

reimport 以后我们就可以开始进行我们的 demo 创建了

 

创建表

要测试数据库读写,首先需要在数据库中添加相应的表

CREATE TABLE `customer` ( `CUST_ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `NAME` varchar(100) NOT NULL, `AGE` int(10) unsigned NOT NULL, PRIMARY KEY (`CUST_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

 

 

配置 JDBC

在 servlet-config.xml 中,我们需要加入 mysql 访问的相关参数

<!-- JDBC 配置 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/test" /> <property name="username" value="techlog" /> <property name="password" value="golhcet" /> </bean>

 

 

创建 model

我们在 model 目录下创建 Customer

package com.demo.model; /** * Created by techlog on 15/11/9. */ public class Customer { int custId; String name; int age; public Customer(int cust_id, String name, int age) { this.age = age; this.name = name; this.custId = cust_id; } public int getCustId() { return custId; } public void setCustId(int custId) { this.custId = custId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }

 

 

创建 DAO

然后,我们在 dao 目录下创建用来操作 model 的 DAO 接口,并在 dao.implement 目录中实现这个接口

package com.demo.dao; import com.demo.model.Customer; /** * Created by techlog on 15/11/10. */ public interface CustomerDAO { public void insert(Customer customer); public Customer findByCustomerId(int custId); }

 

 

package com.demo.dao.implement; import com.demo.dao.CustomerDAO; import com.demo.model.Customer; import org.springframework.stereotype.Repository; import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * Created by techlog on 15/11/10. */ @Repository public class JdbcCustomerDAO implements CustomerDAO { @Resource private DataSource dataSource; public void insert(Customer customer) { String sql = "INSERT INTO CUSTOMER " + "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)"; Connection conn = null; try { conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, customer.getCustId()); ps.setString(2, customer.getName()); ps.setInt(3, customer.getAge()); ps.executeUpdate(); ps.close(); } catch (SQLException e) { throw new RuntimeException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) {} } } } public Customer findByCustomerId(int custId) { String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?"; Connection conn = null; try { conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, custId); Customer customer = null; ResultSet rs = ps.executeQuery(); if (rs.next()) { customer = new Customer( rs.getInt("CUST_ID"), rs.getString("NAME"), rs.getInt("Age") ); } rs.close(); ps.close(); return customer; } catch (SQLException e) { throw new RuntimeException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) {} } } } }

 

 

通过 DAO 类实现时的 Repository 注解用于将数据访问层 (DAO 层 ) 的类标识为 Spring Bean

而 Resource 注解使用 byName 的方式执行自动封装,他有一个 name 属性,用于指定 Bean 在配置文件中对应的名字,如果没有指定 name 属性,那么默认值就是字段或者属性的名字,如果使用 byName 匹配失败后,会退而使用 byType 继续匹配,如果再失败,则抛出异常

 

Controller

虽然在工程中,操作 DAO 的应该是 service 层,不过在我们的小 demo 里就没这个必要了

package com.demo.controller; import com.demo.dao.CustomerDAO; import com.demo.model.Customer; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.servlet.ModelAndView; import javax.annotation.Resource; @Controller @RequestMapping(value = "/jdbc") public class HomeController{ @Resource private CustomerDAO customerDAO; @RequestMapping(value = "/insert") public ModelAndView insertandfind(ModelAndView modelAndView) { Customer customer = new Customer(1, "techlog",28); customerDAO.insert(customer); Customer customer1 = customerDAO.findByCustomerId(customer.getCustId()); modelAndView.addObject("customer", customer1.getName()); modelAndView.setViewName("test"); return modelAndView; } }

 

 

这里插入了一条数据并将这条数据又查询出来

 

jsp

<%-- Created by IntelliJ IDEA. User: techlog Date: 15/11/10 Time: 下午7:06 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> <h1>${customer}</h1> </body> </html>

 

 

SQL 类型与 Java 类型对照表
SQL类型JAVA类型set方法update方法
VARCHARjava.lang.StringsetStringupdateString
CHARjava.lang.StringsetStringupdateString
LONGVARCHARjava.lang.StringsetStringupdateString
BITbooleansetBooleanupdateBoolean
NUMERICjava.math.BigDecimalsetBigDecimalupdateBigDecimal
TINYINTbytesetByteupdateByte
SMALLINTshortsetShortupdateShort
INTEGERintsetIntupdateInt
BIGINTlongsetLongupdateLong
REALfloatsetFloatupdateFloat
FLOATfloatsetFloatupdateFloat
DOUBLEdoublesetDoubleupdateDouble
VARBINARYbyte[ ]setBytesupdateBytes
BINARYbyte[ ]setBytesupdateBytes
DATEjava.sql.DatesetDateupdateDate
TIMEjava.sql.TimesetTimeupdateTime
TIMESTAMPjava.sql.TimestampsetTimestampupdateTimestamp
CLOBjava.sql.ClobsetClobupdateClob
BLOBjava.sql.BlobsetBlobupdateBlob
ARRAYjava.sql.ArraysetARRAYupdateARRAY
REFjava.sql.RefSetRefupdateRef
STRUCTjava.sql.StructSetStructupdateStruct

 

当出现异常情况,JDBC 会抛出异常,捕获异常后可以通过下列函数获取异常信息

JDBC SQLException 方法
方法用途
getErrorCode获取与异常关联的 error code
getMessage获取JDBC驱动程序的错误消息的驱动程序处理的错误或获取对数据库错误的Oracle错误号和消息
getSQLState获取XOPEN SQLstate,对于一个JDBC驱动程序错误,这个方法不会返回任何有用信息,对于数据库错误,则返回五位数XOPEN SQLstate的代码
getNextException针对异常链,这个函数可以获取异常链的下一个异常对象
printStackTrace打印当前的异常

 

部分内容整理自:

http://www.mkyong.com/spring/maven-spring-jdbc-example

http://www.tutorialspoint.com/jdbc/jdbc-quick-guide.htm

 






技术帖      web      mvc      sql      database      龙潭书斋      db      java      framework      orm      spring      springmvc      dao      jdbc     


京ICP备15018585号