Spring jdbcTemplate操作数据库

采用druid作为数据库连接池
1、首先，导入jar包

        <!-- jdbcTemplate的jar的依赖信息 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- 事务控制的jar包的依赖信息 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        
        <!-- 数据库连接池的jar的包依赖信息 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.23</version>
        </dependency>

    <!-- mysql数据库驱动jar -->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.32</version>
    </dependency>

2、实例化DataSource对象（DruidDataSource）,初始化属性值（与数据库建立连接的基本信息，数据库连接池的基本信息）

    <!-- 实例化数据源对象，数据源类DruidDataSource;strl+shift+T搜索指定的类 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <!-- 与数据库建立连接的基本信息 -->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test" />
        <property name="username" value="root" />
        <property name="password" value="123" />
        <!-- 数据库连接池的基本信息；最大连接个数；初始化连接个数；最大等待时间；
最小空闲个数 -->
        <property name="maxActive" value="20" />
        <property name="initialSize" value="5" />
        <property name="maxWait" value="60000" />
        <property name="minIdle" value="1" />
    </bean>
    
    <!-- 实例化jdbcTemplate对象 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

3、实例化JdbcTemplate对象（初始化dataSource属性值），实例化Dao层的像（初始化JdbcTemplate属性值）

@Repository(value="userDao")
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<User> getAllUsers() {
        
        return null;
    }

    @Override
    public int addUser(User user) {
        String sql = "insert into user (username,password) values (?,?)";

        return jdbcTemplate.update
(sql, new Object[]{user.getUsername(),user.getPassword()});
    }

查询单条记录

//查询数据，返回单条
    @Override
    public User QueryById(int id) {
        // TODO Auto-generated method stub
        
        String sql="select *from user where id=?";
        return jdbcTemplate.query
(sql,new Object[]{id}, new ResultSetExtractor<User>()
                {

                    @Override
                    public User extractData(ResultSet rs) 
throws SQLException, DataAccessException {
                        // TODO Auto-generated method stub
                        if(rs.next())
                        {
                            User user=new User();
                            user.setUsername(rs.getString("Username"));
                            user.setId(rs.getInt("id"));
                            return user;
                        }
                        return null;
                    }
            
                }
        );
        
        
    }

查询多条记录

@Override
    public List<User> QueryAll() {
        // TODO Auto-generated method stub
        
        String sql="select * from user";
        return jdbcTemplate.query(sql, new RowMapper<User>()
                {
                    //rowNum  索引
                    @Override
                    public User mapRow(ResultSet rs, int rowNum) throws SQLException
 {
                        // TODO Auto-generated method stub
                        User user=new User();
                        user.setUsername(rs.getString("Username"));
                        user.setId(rs.getInt("id"));
                        return user;
                    }
            
                });
        
    }

批量插入

@Override
    public int[] insertBatch(final List<User> list) {
        // TODO Auto-generated method stub
        
        String sql="insert into user (username,password) values(?,?)";
        return jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
            
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                // TODO Auto-generated method stub
                ps.setString(1, list.get(i).getUsername());
                ps.setString(2, list.get(i).getPassword());
                
            }
            
            @Override
            public int getBatchSize() {
                // TODO Auto-generated method stub
                return list.size();
            }
        });
        
    }