十七章 数据库操作

package seventeenth;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.mysql.cj.jdbc.Driver;
 
public class MysqlDemo {
    Connection connection;
    public static void main(String[] args) throws SQLException {
        MysqlDemo mysqlDemo=new MysqlDemo();
        mysqlDemo.getConnection();
        System.out.println("查询学生表数据，结果为：");
        mysqlDemo.selectAll();
        System.out.println("新增三条数据");
        mysqlDemo.add(11,"雯雯",1392379270,"女");
        mysqlDemo.add(11,"凌天",1392449270,"男");
        mysqlDemo.add(11,"赫瑞",1822379270,"男");
        System.out.println("查询电话以182开头的学生信息：");
        mysqlDemo.selectAll();
        System.out.println("修改student表中第5条数据，电话改为131242353");
        //mysqlDemo.update(5,131242353);
        mysqlDemo.selectApart();
        System.out.println("查询学生表中所有男生：");
        mysqlDemo.selectMale("男");
        System.out.println("第十条数据姓名改为大帅哥：");
        mysqlDemo.updateName(10,"大帅哥");
        System.out.println("删除学生表中第三条数据：");
        mysqlDemo.delete(3);
        System.out.println("查询电话以139开头的学生信息：");
        mysqlDemo.selectTelAgain();
        System.out.println("查询学生表中所有女生：");
        mysqlDemo.selectMale("女");
        mysqlDemo.selectAll();//调用增、删、改、查方法
        mysqlDemo.close();
    }
    
        public void selectTelAgain() throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("select * from student where tel like '139%';");
             ResultSet resultSet= preparedStatement.executeQuery();//第四步：执行SQL语句返回结果集
              while(resultSet.next()) {
                  System.out.print(resultSet.getInt("id"+""));
                  System.out.print(resultSet.getString("name"+""));
                  System.out.print(resultSet.getInt("tel"+""));
                  System.out.println(resultSet.getString("sex"));
                  }
              resultSet.close();
              preparedStatement.close();
 
    }
 
        private void updateName(int id, String name) throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("update student set name =?where id=?;");
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, id);
             preparedStatement.executeUpdate();
    }
 
        public void selectMale(String sex) throws SQLException{
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("select * from student where  sex =?;");
            preparedStatement.setString(1, sex);
             ResultSet resultSet= preparedStatement.executeQuery();//第四步：执行SQL语句返回结果集
              while(resultSet.next()) {
                  System.out.print(resultSet.getInt("id"+""));
                  System.out.print(resultSet.getString("name"+""));
                  System.out.print(resultSet.getInt("tel"+""));
                  System.out.println(resultSet.getString("sex"));
    }resultSet.close();
      preparedStatement.close();
 
        }
        public void selectApart() throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("select * from student where tel like '182%';");
             ResultSet resultSet= preparedStatement.executeQuery();//第四步：执行SQL语句返回结果集
              while(resultSet.next()) {
                  System.out.print(resultSet.getInt("id"+""));
                  System.out.print(resultSet.getString("name"+""));
                  System.out.print(resultSet.getInt("tel"+""));
                  System.out.println(resultSet.getString("sex"));
                  }
              resultSet.close();
              preparedStatement.close();
 
        }
        public void delete(int id) throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("delete from student where id=?;");
            preparedStatement.setInt(1, id);
             preparedStatement.executeUpdate();
        
    }
        public void update(int id,int tel) throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("update student set tel =?where id=?;");
            preparedStatement.setInt(1, tel);
            preparedStatement.setInt(2, id);
             preparedStatement.executeUpdate();
        
    }
        public void add(int id,String name,int tel,String sex) throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement
                    ("insert into student values(?,? ,?,?);");
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, name);
            preparedStatement.setInt(3, tel);
            preparedStatement.setString(4, sex);
             preparedStatement.executeUpdate();
    
    }
        public void getConnection() throws SQLException {
            DriverManager.registerDriver(new Driver());//第一步：注册驱动
              connection =DriverManager.getConnection("jdbc:mysql://localhost:3306/school_java","root","1234");//连接数据库，连接用户，连接密码;第二步：获取连接
              }//第六步：关闭连接释放资源
        public void selectAll() throws SQLException {
            PreparedStatement preparedStatement =connection.prepareStatement("select* from student; ");//准备一个对象传入一个mysql语句
              //第三步：获取statement对象
              ResultSet resultSet= preparedStatement.executeQuery();//第四步：执行SQL语句返回结果集
              while(resultSet.next()) {
                  System.out.print(resultSet.getInt("id"+""));
                  System.out.print(resultSet.getString("name"+""));
                  System.out.print(resultSet.getInt("tel"+""));
                  System.out.println(resultSet.getString("sex"));//第五步：遍历结果集
                 
        }
              resultSet.close();
              preparedStatement.close();
          }
        
          
         public void close() throws SQLException {
             connection.close();//关闭连接
         }