JDBC realizes the function of adding deleting modifying and checking database
- 2021-10-16 01:38:21
- OfStack
JDBC, simply speaking, is to use Java to operate the database. The following briefly introduces how to realize the function of adding, deleting and modifying the database.
1. Add data
package cn.itcast.jdbc;
import java.sql.*;
public class JdbcDemo2 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1 Register driver
Class.forName("com.mysql.jdbc.Driver");
//2 , definition sql
String sql = "insert into course values(?,?,?)";
//3 , get the Connection Object
//student Indicates the database you want to operate on
// If it is locakhost : 3306 It can also be abbreviated as "jdbc:mysql:///student"
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
//4 Gets the execution sql Object of
preparedStatement = connection.prepareStatement(sql);
// Pass in parameters
preparedStatement.setInt(1,5);
preparedStatement.setString(2,"JavaWeb");
preparedStatement.setInt(3,88);
//5 , execution sql
int count = preparedStatement.executeUpdate();
//6 , processing results
System.out.println(count);
if (count > 0) {
System.out.println(" Successful addition ");
} else {
System.out.println(" Add failed ");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7 Release resources
// Avoid null pointer exceptions
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
2. Delete data
package cn.itcast.jdbc;
import java.sql.*;
public class JdbcDemo4 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1 Register driver
Class.forName("com.mysql.jdbc.Driver");
//2 Gets the connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
//3 , definition sql
String sql = "delete from course where cno = ?";
//4 Gets the execution sql Object
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,5);
//5 , execution sql
int count = preparedStatement.executeUpdate();
//6 , processing results
System.out.println(count);
if (count > 0) {
System.out.println(" Delete succeeded ");
} else {
System.out.println(" Delete failed ");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7 Release resources
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3. Modify the data
package cn.itcast.jdbc;
import java.sql.*;
public class JdbcDemo3 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1 Register driver
Class.forName("com.mysql.jdbc.Driver");
//2 Gets the connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
//3 , definition sql
String sql = "update course set period = ? where cno = ?";
//4 Gets the execution sql Object
preparedStatement = connection.prepareStatement(sql);
// Setting parameters
preparedStatement.setInt(1,90);
preparedStatement.setInt(2,1);
//5 , execution sql
int count = preparedStatement.executeUpdate();
//6 , processing results
System.out.println(count);
if (count > 0) {
System.out.println(" Modified successfully! ");
} else {
System.out.println(" Modification failed! ");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7 Release resources
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
4. Query data
package cn.itcast.jdbc;
import cn.itcast.domain.Course;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo5 {
/**
* Query all Course Object
* @return
*/
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<Course> list = null;
try {
//1 Register driver
Class.forName("com.mysql.jdbc.Driver");
//2 Get the connection
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
//3 , definition sql
String sql = "select * from course";
//4 Gets the execution sql Object of
preparedStatement = connection.prepareStatement(sql);
//5 , execution sql
resultSet = preparedStatement.executeQuery();
//6 Traversing the result set, encapsulating the object, loading the collection
Course course = null;
list = new ArrayList<Course>();
while (resultSet.next()) {
// Get data
int cno = resultSet.getInt("cno");
String cname = resultSet.getString("cname");
int period = resultSet.getInt("period");
// Create Course Object and assign a value
course = new Course();
course.setCno(cno);
course.setCname(cname);
course.setPeriod(period);
// Load collection
list.add(course);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
System.out.println(list);
}
}
We can find that the operations of adding, deleting and modifying are basically similar statements, and the statements that execute sql are all the same, all of which are preparedStatement. executeUpdate (). However, the query operation is different, returning a result set, and the statement executing sql is preparedStatement. executeQuery ().