<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.rimi</groupId> <artifactId>SpringJDBCDemo</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>SpringJDBCDemo Maven Webapp</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> <version>8.5.4</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.9.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.3.9.RELEASE</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> </dependencies> <build> <finalName>SpringJDBCDemo</finalName> </build> </project>
<?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:jdbc="http://www.springframework.org/schema/jdbc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd"> <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/j1702"></property> <property name="username" value="root"></property> <property name="password" value="123456"></property> </bean> <bean name="jt" class="org.springframework.jdbc.core.JdbcTemplate" > <constructor-arg name="dataSource" ref="datasource"></constructor-arg> </bean> </beans>
package com.rimi.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Map; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class Dao { public static void main(String[] args) { ApplicationContext ac=new ClassPathXmlApplicationContext("ApplicationContext.xml"); JdbcTemplate jt=(JdbcTemplate)ac.getBean("jt"); User user=new User("huawei", "888"); updateUser(jt, user); System.out.println(findAll2(jt)); } //JdbcTemplate findUser public static List<User> findAll(JdbcTemplate jt){ String sql="select * from user"; List<User> list=new ArrayList<>(); jt.query(sql,(rs) -> { list.add(new User(rs.getString("name"),rs.getString("password"))); }); return list; } public static List<Map<String,Object>> findAll2(JdbcTemplate jt){ String sql="select * from user"; return jt.queryForList(sql); } public static List<Map<String,Object>> findForName(JdbcTemplate jt,String name){ String sql="select * from user where name=?"; return jt.queryForList(sql,name); } //JdbcTemplate addUser public static void addUser(JdbcTemplate jt,User user){ List<Object[]> list=new ArrayList<>(); Object[] u={user.getName(),user.getPassword()}; list.add(u); String sql="insert user (name,password) values(?,?)"; int[] i=jt.batchUpdate(sql, list); System.out.println(Arrays.toString(i)); } //JdbcTemplate deleteUser public static void delUser(JdbcTemplate jt,User user){ String sql="delete from user where name=?"; List<Object[]> list=new ArrayList<>(); Object[] u={user.getName()}; list.add(u); int[] i=jt.batchUpdate(sql, list); System.out.println(Arrays.toString(i)); } //JdbcTemplate updateUser public static void updateUser(JdbcTemplate jt,User user){ String sql="update user set password=? where name=?"; List<Object[]> list=new ArrayList<>(); Object[] u={user.getPassword(),user.getName()}; list.add(u); int[] i=jt.batchUpdate(sql, list); System.out.println(Arrays.toString(i)); }
//Connection
public static List<User> findAll(Connection con){ String sql="select * from user"; List<User> list=new ArrayList<>(); PreparedStatement ps=null; ResultSet rSet=null; try { ps=con.prepareStatement(sql); rSet=ps.executeQuery(); while(rSet.next()){ list.add(new User(rSet.getString("name"), rSet.getString("password"))); } } catch (SQLException e) { e.printStackTrace(); } finally { try { ps.close(); } catch (Exception e2) { e2.printStackTrace(); } try { rSet.close(); } catch (Exception e2) { e2.printStackTrace(); } } return list; } }