使用jdbcTemplate 原理是把載入驅動Class.forName("com.mysql.jdbc.Driver"); 和連接資料庫Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sw_datab ...
查詢目標是完成3個功能:
(1)查詢表,返回某一個值。例如查詢表中記錄的條數,返回一個int類型數據
(2)查詢表,返回結果為某一個對象。
(3)查詢表,返回結果為某一個泛型的list集合。
一、查詢表中記錄的條數,返回一個int類型數據的操作方法
使用jdbcTemplate
原理是把載入驅動Class.forName("com.mysql.jdbc.Driver");
和連接資料庫Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sw_database?user=root&password=root");
用一個對象完成DriverManagerDataSource dataSource=new DriverManagerDataSource();
package com.swift;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;
@Component(value="jdbcTemplateDemo")
public class JdbcTemplateDemo {
public int queryCount() {
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database");
dataSource.setUsername("root");
dataSource.setPassword("root");
JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
String sql="select count(*) from sw_user";
int count=jdbcTemplate.qureyForObject(sql, Integer.class);
return count;
}
public boolean update() {
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database");
dataSource.setUsername("root");
dataSource.setPassword("root");
JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
String sql="update sw_user set password=? where username=?";
int count=jdbcTemplate.update(sql,"lunchtime","doomsday");
if(count!=0) {
return true;
}
return false;
}
}
查詢資料庫操作使用JdbcTemplate對象根據數據源直接使用qureyForObject方法完成,第二個參數Integer.class為返回值類型的class。
之前完成需按下邊方法:
PreparedStatement ps=conn.prepareStatement("select count(*) from sw_user");
???ResultSet rs=ps.executeQuery();???有待驗證
上邊代碼使用Spring框架註解生成對象方法
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" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!-- 開啟註解掃描——對象和屬性 --> <context:component-scan base-package="com.swift"></context:component-scan> <!-- 開啟aop註解方法 --> <aop:aspectj-autoproxy></aop:aspectj-autoproxy> </beans>
使用Servlet類進行測試:
package com.swift;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
@WebServlet("/test")
public class ServletTest extends HttpServlet {
private static final long serialVersionUID = 1L;
public ServletTest() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
response.getWriter().append("Served at: ").append(request.getContextPath());
ApplicationContext context=new ClassPathXmlApplicationContext("aop.xml");
JdbcTemplateDemo jdbcTemplateDemo=(JdbcTemplateDemo) context.getBean("jdbcTemplateDemo");
response.getWriter().append(jdbcTemplateDemo.queryCount());
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
運行結果圖