JDBC之——Statement实现对数据库的增删改操作

拼搏现实的明天。 2022-05-19 00:37 307阅读 0赞

### 一、Statement接口引入 ###

> 作用：用于执行静态SQL语句并返回它所生成结果的对象
> 
> int executeUpdate(String sql)  执行给定SQL语句，该语句可能为INSERT、UPDATE、或DELETE语句，或者不返回任何内容的SQL语句（如SQLDDL语句）。
> 
> void close()  立即释放此Statement对象的数据库和JDBC资源，而不是等待该对象自动关闭时发生此操作。

### 二、使用Statement接口实现添加数据操作 ###

demo1:

public class Demo1 {
    	public static void main(String[] args) throws Exception {
    		DbUtil dbUtil = new DbUtil();
    		String sql = "insert into t_book values(null,'java牛逼',888,'B哥',1)";
    		Connection con = dbUtil.getCon();//获取数据库连接
    		Statement stmt = con.createStatement();//获取Statement
    		int result = stmt.executeUpdate(sql);
    		System.out.println("操作的结果："+result+"数据");
    		stmt.close();//关闭statement
    		con.close();//关闭连接
    	}
    }

demo2:

//工具类
    public class DbUtil {
    	//数据库地址
    	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
    	//用户名
    	private static String dbUserName="root";
    	//密码
    	private static String dbPassword="root";
    	//驱动名称
    	private static String jdbcName="com.mysql.jdbc.Driver";
    	/**
    	 * 获取数据库连接
    	 * 	1.加载数据库驱动
    	 *  2.获取数据库连接
    	 */
    	public Connection getCon() throws Exception {
    		Class.forName(jdbcName);//加载数据库驱动
    		Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    		return con;
    	}
    	/**
    	 * 关闭连接
    	 */
    	public void close(Statement stmt,Connection con) throws Exception {
    		if(stmt!=null) {
    			stmt.close();
    		}
    		if(con!=null) {
    			con.close();
    		}
    	}
    }
    
    //Demo2
    public class Demo2 {
    	private static DbUtil dbUtil = new DbUtil();
    	/**
    	 * 添加图书
    	 */
    	private static int addBook(String bookName,float price,String author,int bookTypeId) throws Exception {
    		Connection con = dbUtil.getCon();
    		String sql = "insert into t_book values(null,'"+bookName+"',"+price+",'"+author+"',"+bookTypeId+")";
    		Statement stmt = con.createStatement();//创建Statement
    		int result = stmt.executeUpdate(sql);
    		dbUtil.close(stmt, con);//关闭Statement和连接
    		return result;
    	}
    	public static void main(String[] args) throws Exception {
    		String bookName = "Java你牛";
    		float price = 100;
    		String author = "牛牛";
    		int bookTypeId = 1;
    		int result = addBook(bookName, price, author, bookTypeId);
    		if(result == 1) {
    			System.out.println("添加成功！");
    		}else {
    			System.out.println("添加失败！");
    		}
    	}
    }

**demo3:以对象的方式**

//工具类
    public class DbUtil {
    	
    	//数据库地址
    	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
    	//用户名
    	private static String dbUserName="root";
    	//密码
    	private static String dbPassword="root";
    	//驱动名称
    	private static String jdbcName="com.mysql.jdbc.Driver";
    	
    	/**
    	 * 获取数据库连接
    	 * 	1.加载数据库驱动
    	 *  2.获取数据库连接
    	 */
    	public Connection getCon() throws Exception {
    		
    		Class.forName(jdbcName);//加载数据库驱动
    		
    		Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    		
    		return con;
    	}
    	
    	/**
    	 * 关闭连接
    	 */
    	public void close(Statement stmt,Connection con) throws Exception {
    		if(stmt!=null) {
    			stmt.close();
    		}
    		if(con!=null) {
    			con.close();
    		}
    	}
    }
    
    
    //book对象
    public class Book {
    	private int id;
    	private String bookName;
    	private float price;
    	private String author;
    	private int bookTypeId;
    	
    	public Book(String bookName, float price, String author, int bookTypeId) {
    		super();
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    	}
            。。。//get和set方法省略
    }
    
    //demo3
    public class Demo3 {
    	private static DbUtil dbUtil = new DbUtil();
    	
    	/**
    	 * 添加图书2  以对象的方式
    	 */
    	private static int addBook2(Book book) throws Exception{
    		Connection con = dbUtil.getCon();
    		String sql = "insert into t_book values(null,'"+book.getBookName()+"',"+book.getPrice()+",'"+book.getAuthor()+"',"+book.getBookTypeId()+")";
    		Statement stmt = con.createStatement();//创建Statement
    		int result = stmt.executeUpdate(sql);
    		dbUtil.close(stmt, con);//关闭Statement和连接
    		return result;
    	}
    	public static void main(String[] args) throws Exception {
    		Book book = new Book("Java牛牛2", 99, "牛牛2", 2);
    		int result = addBook2(book);
    		
    	}
    }

## ##

### 三、使用Statement接口实现更新数据操作 ###

//工具类
    public class DbUtil {
    	//数据库地址
    	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
    	//用户名
    	private static String dbUserName="root";
    	//密码
    	private static String dbPassword="root";
    	//驱动名称
    	private static String jdbcName="com.mysql.jdbc.Driver";
    	/**
    	 * 获取数据库连接
    	 * 	1.加载数据库驱动
    	 *  2.获取数据库连接
    	 */
    	public Connection getCon() throws Exception {
    		
    		Class.forName(jdbcName);//加载数据库驱动
    		
    		Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    		
    		return con;
    	}
    	/**
    	 * 关闭连接
    	 */
    	public void close(Statement stmt,Connection con) throws Exception {
    		if(stmt!=null) {
    			stmt.close();
    		}
    		if(con!=null) {
    			con.close();
    		}
    	}
    }
    
    
    //book对象
    public class Book {
    	private int id;
    	private String bookName;
    	private float price;
    	private String author;
    	private int bookTypeId;
    	
    	public Book(String bookName, float price, String author, int bookTypeId) {
    		super();
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    	}
    	
    	public Book(int id, String bookName, float price, String author, int bookTypeId) {
    		super();
    		this.id = id;
    		this.bookName = bookName;
    		this.price = price;
    		this.author = author;
    		this.bookTypeId = bookTypeId;
    	}
    }
    
    //demo测试类
    public class Demo1 {
    	private static DbUtil dbUtil = new DbUtil();
    	private static int updateBook(Book book) throws Exception {
    		Connection conn = dbUtil.getCon();//获取连接
    		String sql = "update t_book set bookName='"+book.getBookName()
    				+"',price="+book.getPrice()+",author='"+book.getAuthor()
    				+"',bookTypeId="+book.getBookTypeId()+" where id="
    				+book.getId();
    		Statement stmt = conn.createStatement();
    		int result = stmt.executeUpdate(sql);
    		dbUtil.close(stmt, conn);//关闭statemet和连接
    		return result;
    	}
    	public static void main(String[] args) throws Exception {
    		Book book = new Book(3, "java牛牛222", 123, "牛哥", 1);
    		int result = updateBook(book);
    		System.out.println("更新了"+result+"数据");
    		if(result==1) {
    			System.out.println("更新成功");
    		}else {
    			System.out.println("更新失败");
    		}
    	}
    }

### 四、使用Statement接口实现删除数据操作 ###

public class Demo2 {
    	private static DbUtil dbUtil = new DbUtil();
    	private static int deleteBook(int id) throws Exception{
    		Connection con = dbUtil.getCon();//获取连接
    		String sql = "delete from t_book where id = "+id;
    		Statement stmt = con.createStatement();//创建Statement
    		int result = stmt.executeUpdate(sql);
    		dbUtil.close(stmt, con);
    		return result;
    	}
    	public static void main(String[] args) throws Exception {
    		int result = deleteBook(3);
    		if(result ==1) {
    			System.out.println("删除成功！");
    		}else {
    			System.out.println("删除失败！");
    		}
    	}
    }