MySQL_(Java)使用JDBC向数据库发起查询请求　　

　　MySQL_(Java)使用JDBC向数据库中插入(insert)数据　　

　　MySQL_(Java)使用JDBC向数据库中删除(delete)数据　　

　　MySQL_(Java)使用JDBC向数据库中修改(update)数据　　

 

　　MySQL数据库中的数据，数据库名garysql，表名garytb，修改id=1用户名密码

 

 

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBC01 {    public static void main(String[] args) throws SQLException  {        update(1,"111");    }    public static void selectAll() throws SQLException {        //注册驱动    使用驱动连接数据库        Connection con = null;        Statement stmt = null;        ResultSet rs = null;        try {            //数据库的连接            con = JDBCUtils.getConnection();                        //数据库的增删改查            stmt = con.createStatement();            //返回一个结果集            rs =stmt.executeQuery("select * from garytb");                        while(rs.next()) {                //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));                System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password"));            }                } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }finally {            JDBCUtils.close(rs, stmt, con);        }    }    //校验用户    public static boolean  selectByUernamePassword(String username,String password) throws SQLException {        Connection con=null;        Statement stmt = null;        ResultSet rs = null;        try {            Class.forName("com.mysql.jdbc.Driver");                        String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";            con = DriverManager.getConnection(url,"root","123456");            stmt =con.createStatement();            String sql = "select * from garytb where username = '"+username+"' and password = '"+password+"'";            //System.out.println(sql);            rs = stmt.executeQuery(sql);                        if(rs.next()) {                return true;            }else {                return false;            }                        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }finally {            if(rs!=null)                rs.close();            if(stmt!=null)                stmt.close();            if(con!=null)                con.close();        }                return false;    }    public static boolean selectByUP2(String username,String password) throws SQLException{        Connection con=null;        Statement stmt = null;        ResultSet rs = null;        try {            Class.forName("com.mysql.jdbc.Driver");                        String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";            con = DriverManager.getConnection(url,"root","123456");                        String sql = "select * from garytb where username = ? and password = ?";            PreparedStatement pstmt = con.prepareStatement(sql);            //添加参数            pstmt.setString(1, username);            pstmt.setString(2, password);            //进行查询            rs = pstmt.executeQuery();                            if(rs.next()) {                return true;            }else {                return false;            }                        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }finally {            if(rs!=null)                rs.close();            if(stmt!=null)                stmt.close();            if(con!=null)                con.close();        }                return false;    }    //pageNumber是页数，第几页，pageCount是每页显示多少个数据    public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException {        //注册驱动    使用驱动连接数据库                Connection con = null;                PreparedStatement stmt = null;                ResultSet rs = null;                try {                    Class.forName("com.mysql.jdbc.Driver");                                        //String url ="jdbc:mysql://localhost:3306/garysql";                    //指定编码查询数据库                    String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";                    String user = "root";                    String password = "123456";                    //建立和数据库的连接                    con = DriverManager.getConnection(url,user,password);                                        stmt = con.prepareStatement("select * from garytb limit ?,?");                    stmt.setInt(1, (pageNumber-1)*pageCount );                    stmt.setInt(2, pageCount);                                        rs = stmt.executeQuery();                                        while(rs.next()) {                        //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));                        System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password"));                    }                                } catch (Exception e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }finally {                    if(rs!=null)                        rs.close();                    if(stmt!=null)                        stmt.close();                    if(con!=null)                        con.close();                }    }    //crud: create read update delete    //插入语句    public static void insert(String username,String password) throws SQLException {        //注册驱动    使用驱动连接数据库        Connection con = null;        PreparedStatement stmt = null;        ResultSet rs = null;        try {            con = JDBCUtils.getConnection();            String sql = "insert into garytb(username,password) values(?,?)";            stmt = con.prepareStatement(sql);            stmt.setString(1, username);            stmt.setString(2, password);            int result =stmt.executeUpdate();// 返回值代表收到影响的行数            System.out.println("插入成功"+username);        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }finally {            JDBCUtils.close(rs, stmt, con);        }    }    //删除语句    public static void delete(int id) throws SQLException {        //注册驱动    使用驱动连接数据库        Connection con = null;        PreparedStatement stmt = null;        ResultSet rs = null;        try {            con = JDBCUtils.getConnection();                        String sql = "delete from garytb where id = ?";            stmt = con.prepareStatement(sql);            stmt.setInt(1, id);            int result =stmt.executeUpdate();// 返回值代表收到影响的行数            if(result>0) {                System.out.println("删除成功");            }else {                System.out.println("删除失败");            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCUtils.close(rs, stmt, con);        }    }    //修改语句    public static void update(int id,String newPassword) throws SQLException {        Connection con = null;        PreparedStatement stmt = null;        ResultSet rs = null;        try {            con = JDBCUtils.getConnection();                        String sql = "update garytb set password = ? where id = ?";            stmt = con.prepareStatement(sql);            stmt.setString(1, newPassword);            stmt.setInt(2, id);            int result =stmt.executeUpdate();// 返回值代表收到影响的行数            if(result>0) {                System.out.println("修改成功");            }else {                System.out.println("修改失败");            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCUtils.close(rs, stmt, con);        }    }}

 

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtils {        private static final String connectionURL = "jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false";    private static final String username = "root";    private static final String password = "123";        //创建数据库的连接    public static Connection getConnection() {        try {            Class.forName("com.mysql.jdbc.Driver");            return   DriverManager.getConnection(connectionURL,username,password);        } catch (Exception e) {                        e.printStackTrace();        }        return null;    }        //关闭数据库的连接    public static void close(ResultSet rs,Statement stmt,Connection con) throws SQLException {        if(rs!=null)            rs.close();        if(stmt!=null)            stmt.close();        if(con!=null)            con.close();    }}

 

　　根据id修改数据库中的数据

public static void update(int id,String newPassword) throws SQLException {        Connection con = null;        PreparedStatement stmt = null;        ResultSet rs = null;        try {            con = JDBCUtils.getConnection();                        String sql = "update garytb set password = ? where id = ?";            stmt = con.prepareStatement(sql);            stmt.setString(1, newPassword);            stmt.setInt(2, id);            int result =stmt.executeUpdate();// 返回值代表收到影响的行数            if(result>0) {                System.out.println("修改成功");            }else {                System.out.println("修改失败");            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCUtils.close(rs, stmt, con);        }    }