连接数据库
// 数据库连接
public static Connection DB() throws Exception {
Connection conn;
// 定义数据库的参数
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String username = "你的Oracle用户名";
String password = "密码";
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(url, username, password);
return conn;
}
创建数据表
// 创建表
public void create(String tablename) throws Exception {
Connection conn = DB(); // 调用连接方法进行连接
// username不能以数字开头,否则不成功
String sql = "create table "+tablename+" (username varchar(20) primary key, nickname varchar(15), phone varchar(15), password varchar(20) not null);";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sql);
close(); // 调用关闭方法关闭数据库
System.out.println("数据表创建成功");
}
添加表数据
// 数据库插入操作
public void add(User user) throws Exception {
Connection con = DB(); // 调用连接方法进行连接
String sql = "insert into users values(?,?,?,?)";
PreparedStatement preStmt = con.prepareStatement(sql);
preStmt.setString(1, user.getUsername());
preStmt.setString(2, user.getNickname());
preStmt.setString(3, user.getPhone());
preStmt.setString(4, user.getPassword());
// 也可以是preStmt.setInt(5, 520);
preStmt.executeUpdate();
close();
System.out.println("数据插入成功");
}
删除表数据
// 删除数据
public void delete(String username) throws Exception {
Connection con = DB(); // 调用连接方法进行连接
String sql = "delete users where username=?";
PreparedStatement preStmt = con.prepareStatement(sql);
preStmt.setString(1, username);
preStmt.executeUpdate();
close();
System.out.println("数据删除成功");
}
修改表数据
// 更新信息
public void update(User user) throws Exception {
Connection con = DB(); // 调用连接方法进行连接
String sql = "update users set nickname=?, phone=? where username=?";
PreparedStatement preStmt = con.prepareStatement(sql);
preStmt.setString(1, user.getNickname());
preStmt.setString(2, user.getPhone());
preStmt.setString(3, user.getUsername());
preStmt.executeUpdate();
close();
System.out.println("信息更新成功");
}
查询表数据
// 获取用户所有信息
public User find(String username) throws Exception {
Connection con = DB(); // 调用连接方法进行连接
String sql = "select * from users where username='" + username + "'";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
User user = new User();
while (rs.next()) {
user.setUsername(rs.getString(1));
user.setNickname(rs.getString(2));
user.setPhone(rs.getString(3));
user.setPassword(rs.getString(4));
}
close();
System.out.println("查询成功"+user.getUsername());
return user;
}
删除数据表
// 删除表
public void drop(String tablename) throws Exception {
Connection conn = DB(); // 调用连接方法进行连接
String sql = "drop table "+tablename;
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
close();
System.out.println("数据表删除成功");
}