spring-jdbc Demo

<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;
    }
}