JDBC操作数据库
1.jdbc对数据库的更新操作(Statement)
重要方法:
-
conn.createStatement();//创建Statement对象
-
executeUpdate(sql)//进行更新操作,返回结果为被修改的行数`
注意事项:使用statement时,sql语句中使用拼接字符串方式拼接变量,需要在“”拼接外再加上单引号与数据库查询保持一致
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/exam?serverTimezone=UTC";
conn = DriverManager.getConnection(url, "root", "root");
sta = conn.createStatement();
Scanner sc = new Scanner(System.in);
System.out.println("请输入名字");
String ename = sc.next();
System.out.println("请输入职业");
String job = sc.next();
System.out.println("请输入薪资");
int sal = sc.nextInt();
String sql = "insert into emp(ename,job,sal) values('" + ename + "','" + job + "'," + sal + ")";
int i = sta.executeUpdate(sql);
if (i > 0) {
System.out.println("操作成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sta != null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.jdbc对数据库的查询操作
重要方法:
-
sta.executeQuery(sql);//返回ResultSet对象结果集
-
next();//将光标移至指向结果集的下一行
注意事项:ResultSet结果集的遍历取值方式
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/exam?serverTimezone=UTC";
conn = DriverManager.getConnection(url, "root", "root");
sta = conn.createStatement();
String sql = "select * from emp";
rs = sta.executeQuery(sql);
while(rs.next()){
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
System.out.println(empno+"-----"+ename+"------"+job);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(sta!=null){
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.1ResultSet结果集的遍历方式
注意:取值的方法一定要与取到的数据库中的数据类型一一对应,否则会报错
2.1.1使用列下标遍历结果集
while(rs.next()){
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
System.out.println(empno+"-----"+ename+"------"+job);
}
2.1.2使用列名遍历结果集
while(rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
int sal = rs.getInt("sal");
System.out.println(empno+"-----"+ename+"------"+sal);
}
3.jdbc对数据库的更新操作(PreparedStatement)
public static void main(String[] args) {
PreparedStatement ps = null;
Connection conn = null;
try {
Properties pro = new Properties();
FileInputStream is = new FileInputStream("配置文件路径");
pro.load(is);
String driver = pro.getProperty("driver");
String url = pro.getProperty("url");
String username = pro.getProperty("username");
String password = pro.getProperty("password");
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
String sql="insert into emp(ename,job,sal) values (?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1,"张三");
ps.setString(2,"java");
ps.setInt(3,20000);
int i = ps.executeUpdate();
if(i>0){
System.out.println("操作成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.使用JDBCUtils操作数据库
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConn();
String sql = "select * from emp";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String ename = rs.getString("ename");
String job = rs.getString("job");
int sal = rs.getInt("sal");
System.out.println(ename+"———"+job+"———"+sal);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.closeConn(conn,ps,rs);
}
}