java对mysql_JAVA对Mysql进行连接、插入、修改、删除操作

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 null

auto_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 {

String

drivename="com.mysql.jdbc.Driver";

String

url="jdbc:mysql://localhost/shuishengmu";

String

user="root";

String

password="111111";

String

insql;

String

upsql;

String

delsql;

String

sql="select * from user";

String

name;

Connection conn;

ResultSet

rs=null;

public

Connection 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(){

return

userid;

}

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);

}

}