JDBC

 

   

JDBC

学生管理系统的增删改查

//连接数据库

           try{

              Class.forName("com.mysql.jdbc.Driver");

              Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "1234");

              Statement stat=con.createStatement();

              int n=stat.executeUpdate("insert into student values('"+num+"','"+name+"','"+sex+"','"+class1+"','"+home+"');");

              if(n>0){

                  JOptionPane.showMessageDialog(this,"添加成功");

              }else{

                  JOptionPane.showMessageDialog(this,"添加失败");

              }

             

           } catch (ClassNotFoundException e1) {

              // TODO Auto-generated catch block

              System.out.println("");

           }catch (SQLException e2) {

              // TODO Auto-generated catch block                    

              e2.printStackTrace();

           }

       }

       if(s.equals("删除")){

           String num = tfNum.getText();

      

           try{

              Class.forName("com.mysql.jdbc.Driver");//载入驱动

              Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","1234");//建立连接

              Statement stat = con.createStatement();

              int n=stat.executeUpdate("delete * from student where id='"+num+"'");

              if(n>0){

                  JOptionPane.showMessageDialog(this,"删除成功");

              }else{

                  JOptionPane.showMessageDialog(this,"删除失败");

              }

             

           } catch (ClassNotFoundException e1) {

              // TODO Auto-generated catch block

              e1.printStackTrace();

           }catch (SQLException e2) {

              // TODO Auto-generated catch block

              e2.printStackTrace();

           }

       }

       if(s.equals("修改")){

           try{

              String num = tfNum.getText();

              String name = tfName.getText();

              String sex = tfSex.getText();

              String class1= tfClass.getText();

              String home = tfHome.getText();

              Class.forName("com.mysql.jdbc.Driver");//载入驱动

              Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","1234");//建立连接

              Statement stat = con.createStatement();

              int n=stat.executeUpdate("update student set name='"+name+"',sex='"+sex+"',class='"+class1+"',home='"+home+"' where id='"+num+"';");

              if(n>0){

                  JOptionPane.showMessageDialog(this,"修改成功");

              }else{

                  JOptionPane.showMessageDialog(this,"修改失败");

              }

             

           } catch (ClassNotFoundException e1) {

              // TODO Auto-generated catch block

              e1.printStackTrace();

           }catch (SQLException e2) {

              // TODO Auto-generated catch block

              e2.printStackTrace();

           }

       }

       if(s.equals("查询")){

           String num = tfNum.getText();

           try {

              //载入驱动

              Class.forName("com.mysql.jdbc.Driver");

              //建立连接

              Connection con = DriverManager.getConnection(

                     "jdbc:mysql://localhost:3306/student", "root", "1234");

              Statement stat = con.createStatement();

              ResultSet res = stat.executeQuery("select * from student where id='"+num+"'");

              while(res.next()){

                  tfName.setText(res.getString("name"));

                  tfSex.setText(res.getString("sex"));

                  tfClass.setText(res.getString("class"));

                  tfHome.setText(res.getString("home"));

              }

             

           } catch (ClassNotFoundException e1) {

              // TODO Auto-generated catch block

              e1.printStackTrace();

           } catch (SQLException e2) {

              // TODO Auto-generated catch block

              e2.printStackTrace();

           }

 

//封装数据库操作的对象

    private Connection conn;

    private PreparedStatement ps;

    private ResultSet rs;

添加的书写过程

/* 第一步：声明返回结果变量 */

       Student s = new Student();

           /* 第二步：获取连接对象 */

           conn = JdbcUtil.getConnection();

           /* 第三步:定义sql语句 */

           String sql = "select id,name,sex,resume,image from student where id=?";

           /* 第四步:根据sql语句创建预处理对象 */

           ps = conn.prepareStatement(sql);

           /* 第五步：为站位符赋值 */

           int index = 1;

           ps.setObject(index++, id);

           /* 第六步：执行查询 */

           rs = ps.executeQuery();

           /* 第七步：判断 */

           if (rs.next()) {

              s.setId(rs.getString("id"));

              s.setName(rs.getString("name"));

              s.setSex(rs.getString("sex"));

              s.setResume(rs.getString("resume"));

              s.setImage(rs.getBinaryStream("image"));

           }

           /* 第八步：释放资源 */

           JdbcUtil.release(rs, ps,conn);

 

/*工具类 封装了数据库的连接的必备操作*/

public class JdbcUtil {

       private static String url;

       private static String user;

       private static String password;

       private static String driver;

 

       /*

        * 注册驱动

        */

       static {

              try {

                     Properties prop = new Properties();

                     InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("connConfig.properties");

                     //InputStream in=new FileInputStream("./src/connConfig.properties");    

                     prop.load(in);

                     url = prop.getProperty("url");

                     user = prop.getProperty("user");

                     password = prop.getProperty("password");

                     driver = prop.getProperty("driver");

                     Class.forName(driver);

              } catch (Exception e) {

                     throw new RuntimeException(e);

              }

       }

 

       /*

        * 获取连接

        */

       public static Connection getConnection() throws SQLException {

              return DriverManager.getConnection(url, user, password);

       }

 

       /*

        * 释放资源

        */

       public static void release(ResultSet rs, PreparedStatement pstmt, Connection conn) {

              if (rs != null)

                     try {

                            rs.close();

                     } catch (SQLException e) {

                            e.printStackTrace();

                     }

              if (pstmt != null)

                     try {

                            pstmt.close();

                     } catch (SQLException e) {

                            e.printStackTrace();

                     }

              if (conn != null)

                     try {

                            conn.close();

                     } catch (SQLException e) {

                            e.printStackTrace();

                     }

       }

       public static void release1(ResultSet rs, Statement st, Connection conn) {

              if (rs != null)

                     try {

                            rs.close();

                     } catch (SQLException e) {

                            e.printStackTrace();

                     }

                     if (st != null)

                            try {

                                   st.close();

                            } catch (SQLException e) {

                                   e.printStackTrace();

                            }

                            if (conn != null)

                                   try {

                                          conn.close();

                                   } catch (SQLException e) {

                                          e.printStackTrace();

                                   }

       }

 

}