jdbc对mysql的增删改查语句_jdbc连接数据库及对数据库实现增删改查

public class JdbcDemo {

//MySql

public static final String DRIVER = "com.mysql.jdbc.Driver";

public static final String URL = "jdbc:mysql://localhost:port/databse";

//Oracle

//public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";

//public static final String URL = "jdbc:oracle:thin:@host:port:databse";

//SQL Server

//public static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver ";

//public static final String URL = "jdbc:sqlserver://host:port;database","user","password";

public static final String DBNAME = "root";

public static final String DBPASS = "123456";

//连接数据库

public static Connection getConn()

{

Connection conn=null;

try{

Class.forName(DRIVER);//必须把驱动类导入到项目来，才能完成加载

conn=DriverManager.getConnection(URL,DBNAME,DBPASS);

}catch(Exception e){

System.err.println("警告：数据库连接失败！");

}

return conn;

}

//关闭数据库

public static void close(Connection conn)

{

if(conn!=null)

try{

conn.close();

}catch(SQLException e){

e.printStackTrace();

}

}

}

//以对用户表为例子，其中user表包含number,nickname和password三个字段，进行演示

public class DBOperation{

//添加用户----->增

public void addUser(User user) throws Exception {

Connection conn = JdbcDemo.getConn();//获取连接

PreparedStatement prep = conn.prepareStatement("insert into user values(?,?,?)");

prep.setString(1, user.getNumber());//设置第一个参数

prep.setString(2, user.getNickName());//设置第二个参数

prep.setString(3, user.getPassword());//设置第三个参数

prep.executeUpdate();//执行语句

JdbcDemo.close();//关闭连接

}

//删除用户----->删

public void deleteUser(String number) throws Exception{

Connection conn = JdbcDemo.getConn();//获取连接

PreparedStatement prep = conn.prepareStatement("delete from user where number = ?");

prep.setString(1, number);//设置第一个参数

prep.executeUpdate();//执行语句

JdbcDemo.close();//关闭连接

}

//修改用户信息---------->改

public void updateUser(User user) throws Exception {

Connection conn = JdbcDemo.getConn();//获取连接

PreparedStatement prep = conn.prepareStatement("update user set nickname=?,password=? where number=?");

prep.setString(1, user.getNickName());

prep.setString(2, user.getPassword());

prep.setString(3,user.getNumber());

prep.executeUpdate();

JdbcDemo.close();//关闭连接

}

//查询所有用户------------->查

public List findAllUser() throws Exception {

Connection conn = JdbcDemo.getConn();//获取连接

PreparedStatement prep = conn.prepareStatement("select * from user");

ResultSet rst = prep.executeQuery();//执行查询语句，把查询结果保存到结果集rst中

ArrayListlist = new ArrayList();

while(rst.next()){

User user = new User();

user.setNumber(rst.getString("number"));

user.setNickName(rst.getString("nickname"));

user.setPassword(rst.getString("password"));

list.add(user);

}

return list;

}

}