通过java对数据库的增删查修操作

一 准备工作：

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序

首先要安装好 【Mysql数据库】，【SQLyog】，【Eclipse】。

SQLyog:其作用是连接数据库对数据库操作的图形化界面。

 

通过SQLyog 在数据库中新建一个名为st的数据库并在下面建立一个名为student的表。

包含三个字段 id 主键自增；name;tel;

二，具体实现

2.1 在eclipse中新建一个工程名为 jdbc 在该工程下鼠标右键创建一个文件夹用来存放即将导入的mysql-connector-java-5.0.3-bin.jar包。然后直接将mysql-connector-java-5.0.3-bin.jar包复制到该文件夹下。右键鼠标键找到build path然后点击第一个选项将包导入。

2.2 新建一个包，在该包下编写增删查修四个java的程序。

2.3 新建一个包，该包下放抽象出来的对数据库驱动加载和连接的java程序，和一个Student类 包含三个private 属性id , name, tel 并建立get 和set方法。

程序代码：

1，抽象出来的数据库java包：

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

public class Mod {
private final static String driver = "com.mysql.jdbc.Driver";
private final static String url = "jdbc:mysql://localhost:3306/db_students";
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}//加载SQL驱动
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(url,"root","root");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;

}//建立连接
public static void close(ResultSet rs, Statement st, Connection conn)
{
try
{
if(rs != null)
{
rs.close();
}
if(st != null)
{
st.close();
}
if(conn != null)
{
conn.close();
}

}catch(Exception ex)
{
ex.printStackTrace();
}
}
public static void close(Statement st, Connection conn)
{
close(null,st,conn);
}
}

,

2，SearchById ：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.edu.hpu.jdbc1.Mod;
import cn.edu.hpu.jdbc1.Students;

public class SearchById {

public static void main(String[] args) {
int i = 1;
System.out.println(GetList(i).getId());
System.out.println(GetList(i).getName());
System.out.println(GetList(i).getTel());
}
public static Students GetList(int id){

Students Stu = new Students();
Connection C =null;
Statement S=null;
ResultSet R =null;
String sql = "select * from student where id ="+id;
C = Mod.getConnection();
try {
S = C.createStatement();
R = S.executeQuery(sql);
if(R.next()){
Stu.setId(R.getInt("id"));
Stu.setName(R.getString("name"));
Stu.setTel(R.getString("tel"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
Mod.close(R, S, C);
}
return Stu;
}
}

3 Student类：

public class Students {
private int id;
private String name ;
private String tel;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}

}

4 插入操作：

public class AddTest {
private final static String driver = "com.mysql.jdbc.Driver";
private final static String url = "jdbc:mysql://localhost:3306/db_students";
public static void main(String[] args) throws SQLException {
Connection C = null;

// Jdbc1 D = new Jdbc1();
// D.
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

try {
C = DriverManager.getConnection(url);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

String sql = "insert into students(name,tel) values(?,?)";
PreparedStatement P = C.prepareStatement(sql);
P.setString(1, "bendan ");
P.setString(3, "shagua");
int rows = P.executeUpdate();
if(rows>0){
System.out.println("wancheng");
}
else
System.out.println("charushibai");
System.out.println("操作完成");
}

}

5 删除操作：

public class DelTest {

public static void main(String[] args) {
del(2);
}
public static boolean del(int id){
Mod M = new Mod();
Connection C = null;
PreparedStatement P = null;
boolean flag = false;
C = M.getConnection();
String St= "delete from student where id=?";

try {
P = C.prepareStatement(St);
P.setInt(1, id);

int rows = P.executeUpdate();
if(rows>0)
flag =true;
else
flag = false;

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return flag;
}

}

6，查找操作：

public class SearchById {

public static void main(String[] args) {
int i = 1;
System.out.println(GetList(i).getId());
System.out.println(GetList(i).getName());
System.out.println(GetList(i).getTel());

}
public static Students GetList(int id){

Students Stu = new Students();
Connection C =null;
Statement S=null;
ResultSet R =null;
String sql = "select * from student where id ="+id;
C = Mod.getConnection();
try {
S = C.createStatement();
R = S.executeQuery(sql);
if(R.next()){
Stu.setId(R.getInt("id"));
Stu.setName(R.getString("name"));
Stu.setTel(R.getString("tel"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
Mod.close(R, S, C);
}
return Stu;
}

}

7 更新操作：

public class UpdateTest {

public static void main(String[] args) {

SearchById Get = new SearchById();
Students Stu = Get.GetList(5);

Stu.setName("xiaozhu");
Stu.setTel("212");

if(Update(Stu))
System.out.println("更新成功");
else
System.out.println("更新失败");
}
public static boolean Update(Students Stu){
Mod M = new Mod();
boolean flag = false;
// Connection C =null;
// PreparedStatement P = null;
// String sql="update student set name=? tel=? where id=?";
// C = M.getConnection();
try {
Connection C =null;
PreparedStatement P = null;
String sql="update student set name=?,tel=? where id=?";
C = M.getConnection();
P = C.prepareStatement(sql);
P.setString(1, Stu.getName());
P.setString(2, Stu.getTel());
P.setInt(3, Stu.getId());
int rows = P.executeUpdate();
if(rows>0){
flag =true;
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
M.close(P, C);
}
return flag;
}
}