JDBC操作Mysql数据库

package jdbc;


import java.sql.*;




public class TestMysql {
    private static Connection con;
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/java?characterEncoding=utf-8"; // 后面加的?characterEncoding=utf-8 是为了解决乱码问题
    private static String user = "root";
    private static String password = "1234";


    public static void main(String[] args) throws Exception {
//        TestMysql.CUD();
        TestMysql.R();
    }


    public static void R() throws Exception {
        // 查询操作模型
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password); // 获取数据库连接对象
//        String sql1 = "select dep_city_nm,arr_city_nm,dep_airpt_iata,arr_airpt_iata,priority,website from leg";
//        PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
//        ResultSet resultSet = preparedStatement1.executeQuery();    需要ResultSet这个类进行接收


        // 实现限定查询
        String sql2 = "select dep_city_nm,arr_city_nm,dep_airpt_iata,arr_airpt_iata,priority,website from leg where dep_airpt_iata=?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql2);
        preparedStatement1.setObject(1,"CTU");
        ResultSet resultSet = preparedStatement1.executeQuery();  //  需要ResultSet这个类进行接收


        while (resultSet.next()) { // 移动指针,同时判断是否还有数据
            String dep_city_nm = resultSet.getString("dep_city_nm");
//                String dep_city_nm = resultSet.getString(1);   也可以是这样写
            String arr_city_nm = resultSet.getString("arr_city_nm");
            String dep_airpt_iata = resultSet.getString("dep_airpt_iata");
            String arr_airpt_iata = resultSet.getString("arr_airpt_iata");
            String website = resultSet.getString("website");
            int num1 = resultSet.getInt("priority");
            System.out.println(dep_city_nm + "," + arr_city_nm + ", " + dep_airpt_iata + " ,"  + arr_airpt_iata + " ," + website + " ," + num1);
        }


    }




    public static void CUD() throws Exception {
        // 更新 修改 删除都可以用下面的这种模型
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password); // 获取数据库连接对象
        try {
            connection.setAutoCommit(false);  // 取消事物自动提交
            String sql = "insert into leg (dep_city_nm,arr_city_nm,dep_airpt_iata,arr_airpt_iata,priority,website) values (?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);  // 预处理sql
            preparedStatement.setObject(1, "成都");
            preparedStatement.setObject(2, "北京");
            preparedStatement.setObject(3, "CTU");
            preparedStatement.setObject(4, "PEK");
            preparedStatement.setObject(5, 1);
            preparedStatement.setObject(6, "LY");
            int r = preparedStatement.executeUpdate();  // 执行
            System.out.println(r);
            connection.commit();
            connection.close();
        } catch (Exception e) {
            connection.rollback();
            e.printStackTrace();
        }
    }
}




复制