/**
* 1.加载mysql驱动：com.mysql.jdbc.Driver
* 2.获取连接:与数据库建立连接
* 3.sql预编译对象:预编译对象
* 4.执行sql语句
* 5.释放资源
* @author 郑清
*/
public class JDBC_CRUD_Demo {
public static void main(String[] args) {
testCreateDatabase();//创建数据库
add();//添加数据
delete();//删除数据
update();//修改数据
select();//查找数据
}
public static void testCreateDatabase() {
Connection connection = null;
Statement statement = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接：与数据库建立连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhengqing_test", "root", "root");
//3.获得sql的预编译对象
statement = connection.createStatement();
//4.执行sql语句 ex:创建表t_user
String sql = "CREATE TABLE t_user("
+" `id` int(11) NOT NULL,"
+" `username` varchar(20) NOT NULL,"
+" `password` int(11) NOT NULL,"
+" PRIMARY KEY (`id`)"
+" ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
statement.executeUpdate(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.释放资源 (注意：关闭资源顺序 先打开后关闭)
try {
if(statement!=null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connection!=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void add() {
Connection connection = null;
Statement statement = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接：与数据库建立连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhengqing_test", "root", "root");
//3.获得sql的预编译对象
statement = connection.createStatement();
//4.执行sql语句 ex:插入数据到表t_user
String sql = "insert into t_user(id,username,password) values(2,'张三','123'),(10,'李四','123456');";
statement.executeUpdate(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.释放资源 (注意：关闭资源顺序 先打开后关闭)
try {
if(statement!=null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connection!=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void delete() {
Connection connection = null;
Statement statement = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接：与数据库建立连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhengqing_test", "root", "root");
//3.获得sql的预编译对象
statement = connection.createStatement();
//4.执行sql语句 ex:删除表t_user中的数据
String sql = "delete from t_user where id = 2;";
statement.executeUpdate(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.释放资源 (注意：关闭资源顺序 先打开后关闭)
try {
if(statement!=null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connection!=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void update() {
Connection connection = null;
Statement statement = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接：与数据库建立连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhengqing_test", "root", "root");
//3.获得sql的预编译对象
statement = connection.createStatement();
//4.执行sql语句 ex:修改表t_user中数据
String sql = "update t_user set username='修改名字',password='111111' where id=3;";
statement.executeUpdate(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.释放资源 (注意：关闭资源顺序 先打开后关闭)
try {
if(statement!=null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connection!=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void select() {
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接：与数据库建立连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhengqing_test", "root", "root");
//3.获得sql的预编译对象
statement = connection.createStatement();
//4.执行sql语句 ex:查找表t_user中数据
//查找id为3的一条数据
/*String sql = "select * from t_user where id = 3;";
rs = statement.executeQuery(sql);
System.out.println(rs.next());
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("username"));
System.out.println(rs.getString("password"));*/
//查找表中全部数据
String sql = "select * from t_user;";
rs = statement.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getInt("id")+"-->");
System.out.print(rs.getString("username")+"-->");
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.释放资源 (注意：关闭资源顺序 先打开后关闭)
try {
if(rs!=null)rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(statement!=null)statement.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connection!=null)connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
