/*JDBC 程序访问数据库的步骤(用第一种Statement举例,详见下面代码)
private static String CLASSDRIVRE = "com.mysql.jdbc.Driver";
private static String USERNAME = "root";
private static String PASSWORD = "root";
步骤一:加载 JDBC 驱动程序
Class.forName(CLASSDRIVRE);
步骤二:提供连接 URL
/private static String URL = "jdbc:mysql://localhost:3306/mydb"; mytb为你要访问的数据库
步骤三:建立一个数据库的连接
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
步骤四:创建一个 statement
Statement stat = conn.createStatement();
步骤五:创建 SQL 语句
String sqlInsert = "insert into user values(1002,'云松2','123','女',18)"; user 为你要操作的表
步骤六:执行 SQL 语句
statement.executeUpdate(sqlInsert);
步骤七:关闭 JDBC 对象
conn.close();
statement.close();*/
增删改直接处理,sql语句一次性写全了(用Statement)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo {
/**
* @param args
*/
private static String URL = "jdbc:mysql://localhost:3306/mydb";
private static String USERNAME = "root";
private static String PASSWORD = "1234";
private static String CLASSDRIVER = "com.mysql.jdbc.Driver";
public static void main(String[] args) {
// try {
// insertData();
// } catch (Exception e) {
// e.printStackTrace();
// }
// try {
// insertAllData();
// } catch (Exception e) {
// e.printStackTrace();
// }
// try {
// deleteData();
// } catch (Exception e) {
// e.printStackTrace();
// }
// try {
// updateData();
// } catch (Exception e) {
// e.printStackTrace();
// }
// try {
// findUserById(2);
// } catch (Exception e) {
// e.printStackTrace();
// }
try {
List
users = findAllUser();
for (User user : users) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//查找所有的用户信息
private static List
findAllUser() throws Exception {
List
users = new ArrayList<>();
User user = null;
Class.forName(CLASSDRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = conn.createStatement();
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt(1));
user.setName(resultSet.getString(2));
user.setAge(resultSet.getInt(3));
user.setSex(resultSet.getString(4));
users.add(user);
}
conn.close();
statement.close();
resultSet.close();
return users;
}
//根据id查找用户信息
private static void findUserById(int i) throws Exception {
Class.forName(CLASSDRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = conn.createStatement();
String sql = "select *from user where id = "+ i;
ResultSet resultSet = statement.executeQuery(sql);
User user = null;
if (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt(1));
user.setName(resultSet.getString(2));
user.setAge(resultSet.getInt(3));
user.setSex(resultSet.getString(4));
}
conn.close();
resultSet.close();
statement.close();
System.out.println(user);
}
//更新数据
private static void updateData() throws Exception {
Class.forName(CLASSDRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = conn.createStatement();
String sql = "update user set name = '***'where id = 4";
statement.executeUpdate(sql);
conn.close();
statement.close();
}
//删除数据
private static void deleteData() throws Exception {
Class.forName(CLASSDRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = conn.createStatement();
String sql = "delete from user where id = 1";
statement.executeUpdate(sql);
conn.close();
statement.close();
}
//批量添加数据
private static void insertAllData() throws Exception {
Class.forName(CLASSDRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = conn.createStatement();
statement.addBatch("insert into user values(2,'林青霞',25,'女')");
statement.addBatch("insert into user values(3,'令狐冲',26,'男')");
statement.addBatch("insert into user values(4,'赵敏',25,'女')");
statement.addBatch("insert into user values(5,'周芷若',22,'女')");
statement.addBatch("insert into user values(6,'小昭',23,'女')");
statement.executeBatch();
conn.close();
statement.close();
}
//添加数据
private static void insertData() throws Exception {
Class.forName(CLASSDRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement =conn.createStatement();
String sql = "insert into user values(1,'风清扬',45,'男')";
statement.executeUpdate(sql);
conn.close();
statement.close();
}
}
增删改直接处理,sql语句一开始没有写全(用PreparedStatement)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class JDBCTest {
static String DRIVER = "com.mysql.jdbc.Driver";
static String USERNAME = "root";
static String PASSWORD = "1234";
static String URL = "jdbc:mysql://localhost:3306/mydb";
/**
* @param args
*/
public static void main(String[] args) {
Man man = new Man();
// man.setId(1);
// man.setName("小明");
// man.setShows("你是个大傻叉");
//
// man.setId(2);
// man.setName("小红");
// man.setShows("你才是个大傻叉");
//
// man.setId(3);
// man.setName("小花");
// man.setShows("你们是个大傻叉");
man.setId(6);
man.setName("lily");
man.setShows("fafa");
// try {
// insertMan(man);
// } catch (Exception e) {
// e.printStackTrace();
// }
// 8I9JUJKI,OITY6YNGN
// try {
// updataData(1);
// } catch (Exception e) {
// e.printStackTrace();
// }
// try {
// deleteData(2);
// } catch (Exception e) {
// e.printStackTrace();
// }
// try {
// findManById(3);
// } catch (Exception e) {
// e.printStackTrace();
// }
try {
List
mans = findAllMan();
for (Man man2 : mans) {
System.out.println(man2);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//查询所有的用户信息
private static List
findAllMan() throws Exception {
List
mans = new ArrayList<>();
Man man = null;
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
String sql = "select *from man";
PreparedStatement pStatement = conn.prepareStatement(sql);
ResultSet resultSet = pStatement.executeQuery();
while (resultSet.next()) {
man = new Man();
man.setId(resultSet.getInt(1));
man.setName(resultSet.getString(2));
man.setShows(resultSet.getString(3));
mans.add(man);
}
pStatement.close();
conn.close();
return mans;
}
//查询一个用户信息
private static void findManById(int i) throws Exception {
Man man = null;
Class.forName(DRIVER);
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
String sql = "select *from man where id = ?";
PreparedStatement pStatement = connection.prepareStatement(sql);
pStatement.setInt(1, i);
ResultSet resultSet = pStatement.executeQuery();
if (resultSet.next()) {
man = new Man();
man.setId(resultSet.getInt(1));
man.setName(resultSet.getString(2));
man.setShows(resultSet.getString(3));
}
System.out.println(man);
pStatement.close();
connection.close();
}
//删除用户信息
private static void deleteData(int index) throws Exception {
Class.forName(DRIVER);
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
String sql = "delete from man where id = ?";
PreparedStatement pStatement = connection.prepareStatement(sql);
pStatement.setInt(1, index);
pStatement.executeUpdate();
}
//更新用户信息
private static void updataData(int index) throws Exception {
Class.forName(DRIVER);
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
String sql = "update man set name = 'hiro',shows = ? where id = ?";
PreparedStatement pStatement = connection.prepareStatement(sql);
pStatement.setString(1, "hello test");
pStatement.setInt(2, index) ;
pStatement.executeUpdate();
pStatement.close();
connection.close();
}
//插入用户信息
private static void insertMan(Man man) throws Exception {
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
String sql = "insert into man values(?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, man.getId());
preparedStatement.setString(2, man.getName());
preparedStatement.setString(3, man.getShows());
preparedStatement.executeUpdate();
conn.close();
preparedStatement.close();
/*
* 不插入全部数据需在sql语句中指定属性
* String sql = "insert into man (id,shows) values(?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, man.getId());
preparedStatement.setString(2, man.getShows());
preparedStatement.executeUpdate();
conn.close();
preparedStatement.close();*/
}
}