java对数据库进行增删改查

package jdbcmysqlwork;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCOperation {
static class Student {
private String Id;
private String Name;
private String Sex;
private String Age;

    Student(String Name, String Sex, String Age) {
        this.Id = null; //default
        this.Name = Name;
        this.Sex = Sex;
        this.Age = Age;
    }

    public String getId() {
        return Id;
    }

    public void setId(String Id) {
        this.Id = Id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String Name) {
        this.Name = Name;
    }

    public String getSex() {
        return Sex;
    }

    public void setSex(String Sex) {
        this.Sex = Sex;
    }

    public String getAge() {
        return Age;
    }

    public void setage(String Age) {
        this.Age = Age;
    }

}

’ private static Connection getConn() {
String driver = “com.mysql.jdbc.Driver”;
String url = “jdbc:mysql://localhost:3306/student”;
String username = “root”;
String password = “12345678”;
Connection conn = null;
try {
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

‘

private static int insert(Student student) {
    Connection conn = getConn();
    int i = 0;
    String sql = "insert into student (Name,Sex,Age) values(?,?,?)";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        pstmt.setString(1, student.getName());
        pstmt.setString(2, student.getSex());
        pstmt.setString(3, student.getAge());
        i = pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}




private static int update(Student student) {
    Connection conn = getConn();
    int i = 0;
    String sql = "update student set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        i = pstmt.executeUpdate();
        System.out.println("resutl: " + i);
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}


private static Integer getAll() {
    Connection conn = getConn();
    String sql = "select * from student";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        System.out.println(rs);
        int col = rs.getMetaData().getColumnCount();
        System.out.println(col);
        while (rs.next()) {
            for (int i = 1; i <= col; i++) {
                System.out.print(rs.getString(i) + "\t");
                if ((i == 2) && (rs.getString(i).length() < 8)) {
                    System.out.print("\t");
                }
             }
            System.out.println("");
        }
            System.out.println("============================");
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

private static int delete(String name) {
    Connection conn = getConn();
    int i = 0;
    String sql = "delete from student where Name='" + name + "'";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        i = pstmt.executeUpdate();
        System.out.println("result: " + i);
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}


public static void main(String args[]) {
    JDBCOperation.getAll();
    JDBCOperation.insert(new Student("Achilles", "male", "14"));
    JDBCOperation.getAll();
    JDBCOperation.update(new Student("Bean", "male", "7"));
    JDBCOperation.delete("Achilles");
    JDBCOperation.getAll();
}

‘}

在执行增删改查的过程中 有通用的流程
创建Connection对象、sql查询命令字符串；
对connection对象传入sql查询命令，获得preparedstatement对象
对preparedstatement对象执行executeupdate（）或executequrey()获得结果
先后关闭preparedstatement对象和Connection对象。