两种连接数据库的方法-----Jdbc连接数据库---------JdbcTemplate

package util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
 * JDBC工具类 使用Durid连接池
 */
public class JDBCUtils {
    private static DataSource ds ;
    static {
        try {
            //1.加载配置文件
            Properties pro = new Properties();
            //使用ClassLoader加载配置文件，获取字节输入流
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);
            //2.初始化连接池对象
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取连接池对象
     */
    public static DataSource getDataSource(){
        return ds;
    }
    /**
     * 获取连接Connection对象
     */
    public static Connection getConnection() throws SQLException {
        return  ds.getConnection();
    }
}

package cn.itcast.dao;
    import cn.itcast.domain.User;
    import cn.itcast.util.JDBCUtils;
    import org.springframework.dao.DataAccessException;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    /**
     * 操作数据库中User表的类
     */
    public class UserDao {
        //声明JDBCTemplate对象共用
        private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        /**
         * 登录方法
         * @param loginUser 只有用户名和密码
         * @return user包含用户全部数据,没有查询到，返回null
         */
        public User login(User loginUser){
            try {
                //1.编写sql
                String sql = "select * from user where username = ? and password = ?";
                //2.调用query方法
                User user = template.queryForObject(sql,
                        new BeanPropertyRowMapper<User>(User.class),
                        loginUser.getUsername(), loginUser.getPassword());
                           return user;
            } catch (DataAccessException e) {
                e.printStackTrace();//记录日志
                return null;
            }
        }
    }

//增加用户
    @Override
    public void add(User user) {
        //定义SQL
        String sql = "insert into user value(null,?,?,?,?,?,?,null,null)";
        //执行SQL
        template.update(sql,user.getName( ),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
    }
    //删除用户
    @Override
    public void delete(int id) {
        //定义SQL
        String sql = "delete  from  user  where id = ?";
        //执行SQL
        template.update(sql,id);
    }
    //更新用户信息
    @Override
    public Void upate(User user) {
        //定义SQL
        String sql = "update user set name = ?,gender = ?,age = ?,address = ? ,qq = ?,email =? where id = ?";
        //执行SQL
      template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail(),user.getId());
        return null;
    }

public <T> List<T> query(String sql, RowMapper<T> rowMapper)
执行查询语句，返回一个List集合，List中存放的是RowMapper指定类型的数据。

@Override
    public List<User> findAll() {
        //使用jdbc操作数据库
        //1定义sql
        String sql = "select * from user";
        //JdbcTemplate查询-BeanPropertyRowMapper返回User对象
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

//通过姓名和密码来查询用户,返回值是用户
    public User findUserByUsernameAndPassword(String username, String password) {
        try {
            String sql = "select * from user where username = ? and password = ?";
            User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username, password);
            return user;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
//通过id来查询用户
    @Override
    public User finduserByid(int id) {
            String sql = "select * from user where id = ?";
            return template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),id);
    }

package com.itheima.dao.impl;
import com.itheima.dao.ItemsDao;
import com.itheima.domain.Items;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
 * 要想从数据库中取出数据
 * 必须有四个属性：数据库驱动，连接数据库的地址，数据库用户名称，数据库密码。
 */
public class ItemsDaoImpl implements ItemsDao {
    public List<Items> findAll() throws Exception{
        List<Items> list = new ArrayList<Items>();
        //先获取contection对象
        Connection connection = null;
        //获取真正操作数据的对象
        PreparedStatement pst = null;
        //执行数据库查询操作
        ResultSet rs = null;
        try {
            //加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //先获取contection对象
            connection = DriverManager.getConnection("jdbc:mysql:///maven","root", "root");
            //获取真正操作数据的对象
            pst = connection.prepareCall("select * from items");
            //执行数据库查询操作
           rs = pst.executeQuery();
            //把数据库结果集转成java的List集合
            while (rs.next()){
                Items items = new Items();
                items.setId(rs.getInt("id"));
                items.setName(rs.getString("name"));
                list.add(items);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            connection.close();
            pst.close();
            rs.close();
        }
        return list;
    }
}