对数据库的增删改（一个类）

1.建立数据库表

mysql> create database stu;
Query OK, 1 row affected (0.38 sec)

mysql> use stu;
Database changed
mysql> create table student(
    -> id int auto_increment primary key,
    -> name varchar(50),
    -> grent varchar(50),
    -> core varchar(50)
    -> );
Query OK, 0 rows affected (0.47 sec)

2.编写一个数据库连接类（sqlConnection.java）

package org.db;

import java.sql.*;

public class sqlConnection {
 public boolean insert(String name,String grent,String core)
    {
        String sql="insert into student values(null,'"+name+"','"+grent+"','"+core+"')";
        return executesql(sql);
    }
 
    public boolean delete(String id)
   { 
     int k=Integer.parseInt(id);
        String sql="delete from student where id="+k;
        return executesql(sql);
   }
 
    public boolean update(String id,String name,String grent,String core)
    {
     int k=Integer.parseInt(id);
        String sql="update student set name='"+name+"',grent='"+grent+"',core='"+core+"' where id="+k;
        return executesql(sql);
    }
   
    public String[] getOneById(String id)
    {
     return (String[])getAllById(1,id).get(0);
    }
   
    public List getAllById(int a,String id)
    {
     List lst=new ArrayList();
     Connection conn=getConnection();
     
      String sql="";
     if(a==0)
     {
     sql="select * from student";
     }
     else
     {
     int k=Integer.parseInt(id);
        sql="select * from student where id="+k;
     }
     Statement stmt=null;
     try
        {  
     stmt=conn.createStatement();
     ResultSet rs=stmt.executeQuery(sql);
     while(rs.next())
     {  
      String[] st=new String[4];
      st[0]=rs.getInt(1)+"";
      st[1]=rs.getString(2);
      st[2]=rs.getString(3);
      st[3]=rs.getString(4);
      lst.add(st);
      
     }
        }
     catch(Exception e)       
        {                  
           e.printStackTrace();
        }
        finally
        {
          close(stmt,conn);
        }
     return lst;
   
    }
       
   
  
    public static Connection getConnection()
   {
        String user="root";
        String pwd="sql";
        String url="jdbc:mysql://localhost:3306/stu";
        Connection con=null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch(ClassNotFoundException ex)
        {
            System.out.println("数据库驱动不存在");
        }
        try
        {
            con=DriverManager.getConnection(url,user,pwd);
        }
        catch(SQLException ex)
        {
            System.out.println("未连接到数据库");
        }
        return con;
    }
 
    public boolean executesql(String sql)
    {
        Connection conn=getConnection();
        boolean flag=false;
        Statement stmt=null;
        try
       {  
           stmt=conn.createStatement();
           stmt.executeUpdate(sql);
           flag=true;
        }
        catch(Exception e)       
        {                  
            e.printStackTrace();
        }
        finally
       {
           close(stmt,conn);
       }
       return flag;
    }
 
    public static void close(ResultSet rs,Statement stmt,Connection conn)
    {
        close(rs);
        close(stmt);
        close(conn);
    }

    public static void close(Statement stmt,Connection conn)
   {
        close(stmt);
        close(conn);
    }
 
    public static void close(ResultSet rs)
   {
        try
        {
           if(rs!=null)
           {       
               rs.close();
            }
        }
        catch(Exception e)
        {        
            e.printStackTrace();
        }
    }
 
    public static void close(Statement stmt)
    {
        try
        {
           if(stmt!=null)
           {
               stmt.close();
            }
        }
        catch(Exception e)
       {   
           e.printStackTrace();
        }
    }
    public static void close(Connection conn)
    {
        try
       {        
           if(conn!=null)
           {      
               conn.close();
            }
        }
        catch(Exception e)
       {
            e.printStackTrace();
        }
    }
}

 3.编写一个测试类(test.java)

package org.test;
import org.db.*;
import java.sql.*;

public class test {

 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  sqlConnection sc=new sqlConnection();
  sc.getConnection();
  sc.insert("g","1101","90");
  sc.insert("h","1101","80");
  sc.insert("l","1101","70");
  sc.delete("2");
  sc.update("1","z","1101","90");
 }

}

 

运行结果：

mysql> select * from student;
+----+------+-------+------+
| id | name | grent | core |
+----+------+-------+------+
|  1 | z    | 1101  | 90   |
|  3 | l    | 1101  | 70   |
+----+------+-------+------+
2 rows in set (0.00 sec)