book对象
public class Book {
private int id;
private String name;
private String author;
private float price;
private int category_id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public int getCategory_id() {
return category_id;
}
public void setCategory_id(int category_id) {
this.category_id = category_id;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
", category_id=" + category_id +
'}';
}
}
查询
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.*;
public class Select {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/ebook?useSSL=false";
String username = "root";
String password = "111111";
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL(url);
((MysqlDataSource)dataSource).setUser(username);
((MysqlDataSource)dataSource).setPassword(password);
Connection connection = dataSource.getConnection();
int id = 3;
String name = "儒林外史";
String sql = "select * from book where id = ? and name = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
preparedStatement.setString(2,name);
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
Book book = new Book();
book.setId(resultSet.getInt(1));
book.setName(resultSet.getString(2));
book.setAuthor(resultSet.getString(3));
book.setPrice(resultSet.getFloat(4));
book.setCategory_id(resultSet.getInt(5));
System.out.println(book);
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}
添加
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Add {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/ebook?useSSL=false";
String username = "root";
String password = "111111";
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL(url);
((MysqlDataSource)dataSource).setUser(username);
((MysqlDataSource)dataSource).setPassword(password);
Connection connection = dataSource.getConnection();
String sql = "insert into book(name,author,price,category_id) values(?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"数据库应用");
preparedStatement.setString(2,"星星");
preparedStatement.setFloat(3,32.65f);
preparedStatement.setInt(4,3);
int ret = preparedStatement.executeUpdate();
if(ret != 0) {
System.out.println("插入成功");`在这里插入代码片`
}
preparedStatement.close();
connection.close();
}
}
修改
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Update {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/ebook?useSSL=false";
String username = "root";
String password = "111111";
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL(url);
((MysqlDataSource)dataSource).setUser(username);
((MysqlDataSource)dataSource).setPassword(password);
Connection connection = dataSource.getConnection();
String sql = "update book set author = ? where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"狒狒");
preparedStatement.setInt(2,15);
int ret = preparedStatement.executeUpdate();
if(ret != 0) {
System.out.println("更新成功");
}
preparedStatement.close();
connection.close();
}
}
删除
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Delete {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
String url = "jdbc:mysql://127.0.0.1:3306/ebook?useSSL=false";
String username = "root";
String password = "111111";
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL(url);
((MysqlDataSource)dataSource).setUser(username);
((MysqlDataSource)dataSource).setPassword(password);
Connection connection = dataSource.getConnection();
String sql = "delete from book where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,15);
int ret = preparedStatement.executeUpdate();
if(ret != 0) {
System.out.println("删除成功");
}
preparedStatement.close();
connection.close();
}
}