jdbc连接mysql数据库

一、建立名叫jdbc连接mysql数据库的工程（Java和javaweb工程都行）

我用javaweb工程

二、建立MysqlDemo类调用数据库ConnectSql类的各种方法

1、MysqlDemo类以调用select(sql)查询数据库为列

 

public class MysqlDemo {

    public static void main(String[] args) {
        /**
         * 查询数据库
         */
        {
            String sql = "select * from dept";
            ConnectSql c = new ConnectSql();
            try {
                ResultSet rs = c.select(sql);
                while (rs.next()) {
                    System.out.print(rs.getString("deptno")+"  ");
                    System.out.print(rs.getString("deptname")+"  ");
                    System.out.println(rs.getString("age"));
                }
            } catch (Exception e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
    }
}

2、登录mysql数据库

private static final String DBDRIVER = "com.mysql.jdbc.Driver";// MySql数据库驱动
    private static final String databasename = "zxg";//数据库名称
    private static final String DBURL = "jdbc:mysql://localhost:3306/"+databasename;// 连接数据库
    private static final String DBUSER = "root";// 数据库用户登录名称
    private static final String DBPASSWORD = "123";// 数据库用户登录密码
    private static Connection conn = null;
    private static Statement ps = null;
    private static ResultSet rs = null;

    public static Connection getConnection() {
        try {
            Class.forName(DBDRIVER);// 加载MySql数据库驱动
            conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);// 连接数据库
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

3、插入数据

// 插入数据
    public int insert(String sql) throws Exception {
        int num = 0;// 计数器
        try {
            conn = getConnection();
            ps = conn.createStatement();
            ps.executeUpdate(sql);
        } catch (SQLException sqle) {
            throw new SQLException("insert data Exception: " + sqle.getMessage());
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                    num++;
                }
            } catch (Exception e) {
                throw new Exception("ps close exception: " + e.getMessage());
            }
            try {
                if (conn != null) {
                    conn.close();
                    num++;
                }
            } catch (Exception e) {
                throw new Exception("conn close exception: " + e.getMessage());
            }
        }
        return num;
    }

4、修改数据

// 更新 修改数据
    public int update(String sql) throws Exception {
        int num = 0;
        try {
            conn = getConnection();
            ps = conn.createStatement();
            ps.executeUpdate(sql);
        } catch (SQLException sqle) {
            throw new SQLException("update data Exception: " + sqle.getMessage());
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
                throw new Exception("ps close Exception: " + e.getMessage());
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                throw new Exception("conn close Excepiton: " + e.getMessage());
            }
        }
        return num;
    }

5、查询数据

// 查询数据
    public ResultSet select(String sql) throws Exception {
        try {
            conn = getConnection();
            ps = conn.createStatement();
            rs = ps.executeQuery(sql);
            return rs;
        } catch (SQLException sqle) {
            throw new SQLException("select data Exception: " + sqle.getMessage());
        } catch (Exception e) {
            throw new Exception("System error: " + e.getMessage());
        }
    }

6、删除数据

// 删除数据
    public int delete(String sql) throws Exception {
        int num = 0;
        try {
            conn = getConnection();
            ps = conn.createStatement();
            ps.executeUpdate(sql);
        } catch (SQLException sqle) {
            throw new SQLException("delete data Exception: " + sqle.getMessage());
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception e) {
                throw new Exception("ps close Exception: " + e.getMessage());
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                throw new Exception("conn close Exception: " + e.getMessage());
            }
        }
        return num;
    }