JAVA对Mysql进行连接、插入、修改、删除操作。
数据库表单的创建
mysql> create database shuishengmu;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
|Database |
+--------------------+
| information_schema |
|mysql |
|shuishengmu |
|test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use shuishengmu;
Database changed
mysql> create table user(
-> userid int(8) unsigned not nullauto_increment,
-> username varchar(20) not null default'',
-> password varchar(40) not null default'',
-> email varchar(40) not null default'',
-> primary key(userid)
-> )
-> type=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)
******************************************************************************
下面的类是连接、插入、修改、删除操作
package shuishengmu;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ConnectDatabase {
Stringdrivename="com.mysql.jdbc.Driver";
Stringurl="jdbc:mysql://localhost/shuishengmu";
Stringuser="root";
Stringpassword="111111";
Stringinsql;
Stringupsql;
Stringdelsql;
Stringsql="select * from user";
Stringname;
Connection conn;
ResultSetrs=null;
publicConnection ConnectMysql(){
try{
Class.forName(drivename);
conn = (Connection) DriverManager.getConnection(url, user,password);
if (!conn.isClosed()) {
System.out.println("Succeeded connecting to the Database!");
} else {
System.out.println("Falled connecting to the Database!");
}
}catch(Exception ex){
ex.printStackTrace();
}
return conn;
}
public void CutConnection(Connection conn) throws SQLException{
try{
if(rs!=null);
if(conn!=null);
}catch(Exception e){
e.printStackTrace();
}finally{
rs.close();
conn.close();
}
}
class user{//内部类，其字段对应用来存放、提取数据库中的数据
int userid;
String username="";
String password="";
String email="";
//通过set方法，往类的实例里“存放”数据
//通过get方法，从类的实例里“获得”数据，然后再通过插入数据库
public void setId(int userid){
this.userid=userid;
}
public void setName(String username){
this.username=username;
}
public void setPassword(String password){
this.password=password;
}
public void setEmail(String email){
this.email=email;
}
public Integer getId(){
returnuserid;
}
public String getName(){
return username;
}
public String getPassword(){
return password;
}
public String getEmail(){
return email;
}
}
//插入、删除、更新的方法是一样的，不一样的是数据库参数
public boolean InsertSql(user user){
try{
insql="insert into user(userid,username,password,email)values(?,?,?,?)";
//上面的方法比下面的方法有优势，一方面是安全性，另一方面我忘记了……
//insql="insert into user(userid,username,password,email)values(user.getId,user.getName,user.getPassword,user.getEmail)";
PreparedStatement ps=conn.prepareStatement(insql);
//.preparedStatement(insql);
//PreparedStatement ps=(PreparedStatement)conn.prepareStatement(insql);
ps.setInt(1, user.getId());
ps.setString(2, user.getName());
ps.setString(3, user.getPassword());
ps.setString(4, user.getEmail());
int result=ps.executeUpdate();
//ps.executeUpdate();无法判断是否已经插入
if(result>0)
return true;
}catch(Exception e){
e.printStackTrace();
}
return false;
}
//与其他操作相比较，查询语句在查询后需要一个查询结果集（ResultSet）来保存查询结果
public void SelectSql(String sql){
try{
Statement statement=conn.createStatement();
rs=statement.executeQuery(sql);
while(rs.next()){
name=rs.getString("username");
System.out.println(rs.getString("userid")+name+rs.getString("email"));
}
}catch(Exception e){
e.printStackTrace();
}
}
public boolean UpdateSql(String upsql){
try {
PreparedStatement ps = conn.prepareStatement(upsql);
int result=ps.executeUpdate();//返回行数或者0
if(result>0)
return true;
} catch (SQLException ex) {
Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE,null, ex);
}
return false;
}
public boolean DeletSql(String delsql){
try {
PreparedStatement ps = conn.prepareStatement(upsql);
int result=ps.executeUpdate(delsql);
if(result>0)
return true;
} catch (SQLException ex) {
Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE,null, ex);
}
return false;
}
}
*************************************************************************
用来测试上面方法的类：
package shuishengmu;
import java.sql.SQLException;
import shuishengmu.ConnectDatabase.user;
public class Main {
public static void main(String args[]) throws SQLException{
ConnectDatabase cd=new ConnectDatabase();
user user=cd.new user();
cd.ConnectMysql();
user.setId(14);//每次测试运行都要改变该数字
user.setName("zhangsan");
user.setPassword("we23");
user.setEmail("udjdj@125.com");
cd.upsql="update user set username='we' where userid=5";
cd.delsql="delete from user where userid=1";
//cd.InsertSql(user,"kkk");
cd.InsertSql(user);
cd.DeletSql(cd.delsql);
cd.UpdateSql(cd.upsql);
cd.SelectSql(cd.sql);
cd.CutConnection(cd.conn);
}
}
