Servlet的数据库访问

 

创建数据库：

 

 

 

 

 

import java.sql.*;


public class SqlUtil {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver"); //加载驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /*
    * 获取连接
    *
    * */

    public static Connection getConnection(){
        try {
            return  DriverManager.getConnection("jdbc:mysql:///test","root","123456");
            //// JDBC 驱动名及数据库 URL
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /*
    *关闭连接
    * */
    public static void close(ResultSet rs,PreparedStatement pst,Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pst != null){
            try {
               pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
    * 执行注册
    * */

    public static boolean reg(UserEntity user){
        Connection conn = getConnection(); // 打开一个连接
        String sql ="insert into users (username,password,sex,hobby,address,introduce) values (?,?,?,?,?,?)";
        PreparedStatement pst =null;
        boolean flag =false;
        try {
            pst =conn.prepareStatement(sql);
            pst.setString(1, user.getUsername());
            pst.setString(2, user.getPassword());
            pst.setString(3, user.getSex());
            pst.setString(4, user.getHobby());
            pst.setString(5, user.getAdress());
            pst.setString(6, user.getIntroduce());
            flag = pst.executeUpdate() > 0; //执行完成
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(null,pst,conn);
        }
        return flag;
    }

    /*
    * 执行登录
    * */
    public static UserEntity login(String username,String password){
        //获取连接
        Connection conn = getConnection();
        //sql
        String sql = "select * from users where username=? and password=?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        UserEntity user = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, username);
            pst.setString(2, password);
            rs = pst.executeQuery();
            while(rs.next()){
                user = new UserEntity();
                user.setUid(rs.getInt("uid")); //从数据库获取数据 之后将数据存放在user对象中
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setAddress(rs.getString("address"));
                user.setHobby(rs.getString("hobby"));
                user.setIntroduce(rs.getString("introduce"));
                user.setSex(rs.getString("sex"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            close(rs, pst, conn);
        }
        return user;
    }
}