使用jdbc连接数据库

1.数据库连接工具类

/**
 * Author: hezishan
 * Date: 2018/5/15.
 * Description:
 **/
public class DBUtils {

    //资源文件
    static Properties pros = null;
    //静态初始化  当加载JDBCUtil类时调用
    static {
        pros = new Properties();
        try {
            //加载资源文件
            InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            if(in == null) {
                throw new FileNotFoundException("配置文件未找到");
            }
            pros.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return Connection
     */
    public static Connection getMySQLConnection() {
        Connection conn = null;
        try {
            //加载数据库驱动
            Class.forName(pros.getProperty("mysql.driver"));
            //获取数据库连接
            conn = DriverManager.getConnection(pros.getProperty("mysql.url"),
                    pros.getProperty("mysql.user"),pros.getProperty("mysql.pwd"));

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭JDBC资源  注意顺序
    public static void close(ResultSet rs, Statement ps, Connection conn) {
        try {
            if(rs!=null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps!=null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement ps,Connection conn){
        try {
            if(ps!=null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection conn){
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

2.实体类：

**
 * Author: hezishan
 * Date: 2018/5/15.
 * Description:
 **/
public class SearchWordInfo {

    private int id;

    private String searchWord;

    private String lang;

    private int hotIndex;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getSearchWord() {
        return searchWord;
    }

    public void setSearchWord(String searchWord) {
        this.searchWord = searchWord;
    }

    public String getLang() {
        return lang;
    }

    public void setLang(String lang) {
        this.lang = lang;
    }

    public int getHotIndex() {
        return hotIndex;
    }

    public void setHotIndex(int hotIndex) {
        this.hotIndex = hotIndex;
    }
}

3.dao层：

/**
 * Author: hezishan
 * Date: 2018/5/15.
 * Description:
 **/
public interface SearchWordInfoDao {

    /**
     * 保存
     * @param info
     */
   public void saveSearchWordInfo(SearchWordInfo info);
}

/**
 * Author: hezishan
 * Date: 2018/5/15.
 * Description:
 **/
public class SearchWordInfoDaoImpl implements SearchWordInfoDao {


    public void saveSearchWordInfo(SearchWordInfo info) {
        Connection connection = null;
        PreparedStatement ps = null;
        String sql = "insert into search_word_info(search_word,lang,hot_index) values(?,?,?)";
        try {
            connection = DBUtils.getMySQLConnection();
            ps = connection.prepareStatement(sql);
            ps.setString(1, info.getSearchWord());
            ps.setString(2, info.getLang());
            ps.setInt(3, info.getHotIndex());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(ps, connection);
        }
    }
}

    public static SearchInfo getSearchInfoByWord(String word){
        SearchInfo info=null;
        Connection connection = null;
        PreparedStatement ps = null;
        String sql = "select * from search_word_info where search_word =?";
        try {
            connection = DBUtils.getMySQLConnection(2);
            ps = connection.prepareStatement(sql);
            ps.setString(1,word);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                info=new SearchInfo();
                info.setId(rs.getInt(1));
                info.setSearchWord(rs.getString(2));
                info.setLang(rs.getString(3));
                info.setHotIndex(rs.getDouble(4));
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(ps, connection);
        }
        return info;
    }

    public static void updateSearchInfo(SearchInfo info){
        Connection connection = null;
        PreparedStatement ps = null;
        String sql = "update search_word_info set hot_index=? , update_time=? where id=?";
        try {
            connection = DBUtils.getMySQLConnection(2);
            ps = connection.prepareStatement(sql);
            ps.setDouble(1, info.getHotIndex());
            ps.setTimestamp(2, new java.sql.Timestamp(Calendar.getInstance().getTime().getTime()));
            ps.setInt(3,info.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(ps, connection);
        }
    }

    public static void saveSearchInfo(SearchInfo info){
        Connection connection = null;
        PreparedStatement ps = null;
        String sql = "insert into search_word_info(search_word,lang,hot_index,create_time) values(?,?,?,?)";
        try {
            connection = DBUtils.getMySQLConnection(2);
            ps = connection.prepareStatement(sql);
            ps.setString(1, info.getSearchWord());
            ps.setString(2, info.getLang());
            ps.setDouble(3, info.getHotIndex());
            ps.setTimestamp(4, new java.sql.Timestamp(Calendar.getInstance().getTime().getTime()));
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(ps, connection);
        }
    }

db.properties:

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8
mysql.user=root
mysql.pwd=123456

4.测试，在main里面进行测试：

SearchWordInfo info=new SearchWordInfo();
        info.setLang("chinese");
        info.setSearchWord("redis");
        info.setHotIndex(2);
        SearchWordInfoDaoImpl infoDao=new SearchWordInfoDaoImpl();
        infoDao.saveSearchWordInfo(info);

maven项目添加数据库依赖：

  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>