设为首页 | 加入收藏 | 会员中心 | APP下载

程序猿

当前位置:主页 > 程序猿 > SQL >

Statement (操作 SQL 语句)

更新时间:2018-04-16 21:42 | 来源: 网络整理 |

Statement, PreparedStatement, CallableStatement

一旦建立好连接, 就可以与数据库交互. JDBC 中Statement, PreparedStatement 和 CallableStatement 提供了SQL操作的相关API. 其中 CallableStatement 继承自 PreparedStatement, 而 PreparedStatement 又继承自 Statement. 他们的区别是:

《本文来自电脑网 www.yuucn.com 》

Statement 提供基本的 SQL 操作. 适合静态SQL语句, 且传入的 SQL 语句无法接受参数. 《本文来自电脑高手 www.yuucn.com 》

PreparedStatement 可以在 SQL 中传递参数, 适合多次使用的 SQL 语句. 《本文来自电脑网 www.yuucn.com 》

CallableStatement 可以调用 PL/SQL 存储过程. 《本文来自电脑网 www.yuucn.com 》

尽管接口功能有不同, 但是使用方式大体相同, 分以下几步: 《本文来自手机高手 www.yuucn.com 》

创建 Statement 《本文章来自于www.yuucn.com 》

执行 SQL 语句 《本文来自苹果专家 www.yuucn.com 》

关闭 Statement 《本文章来自于www.yuucn.com 》

在执行 SQL 语句的时候, 常用以下几个方法: 《本文来自苹果专家 www.yuucn.com 》

boolean execute(String SQL) : 如果有 ResultSet 产生返回true, 否则, 返回 false. 一般用于 CREATE, ALTER 这些操作, 或者用来检查一个Query有没有返回. 《本文章来自于www.yuucn.com 》

int executeUpdate(String SQL) : 返回被影响的记录的条数, 一般用于 INSERT, UPDATE, DELETE 这些操作.

《本文来自苹果专家 www.yuucn.com 》

ResultSet executeQuery(String SQL) : 返回查询结果集, 专用语 SELECT.

《本文来自电脑网 www.yuucn.com 》

以下三个例子分别示例了如何适用他们. 《本文来自电脑网 www.yuucn.com 》

Statement 例子. 《本文来自自学网 www.yuucn.com 》

public class StatementExample { private Properties dbProps = new Properties(); StatementExample() {} public void setDBProperties(Properties dbProps) { this.dbProps = dbProps; } public Connection getConnection() throws SQLException { String url = dbProps.getProperty("url"); String user = dbProps.getProperty("user"); Connection conn = null; if (user.length() == 0) { conn = DriverManager.getConnection(url); } else { conn = DriverManager.getConnection(url, dbProps); } String dbName = dbProps.getProperty("dbName"); conn.setCatalog(dbName); return conn; } public void deleteAll() throws SQLException { String sql = "DELETE FROM posts"; Connection conn = getConnection(); Statement stmt = conn.createStatement(); int nRows = stmt.executeUpdate(sql); System.out.println(nRows + (nRows == 1 ? " post is " : " posts are ") + "deleted."); stmt.close(); conn.close(); } public void insertPost(Post post) throws SQLException { String sql = "INSERT INTO posts VALUES"; String title = post.getTitle(); String content = post.getContent(); Boolean visible = post.isVisible(); sql += "(" + "NULL" + "," + "\"" +title + "\"" + "," + "\"" + content + "\"" + "," + "DEFAULT" + "," + (visible ? "TRUE" : "FALSE") + ")"; Connection conn = getConnection(); Statement stmt = conn.createStatement(); int nRows = stmt.executeUpdate(sql); stmt.close(); conn.close(); } public ArrayList<Post> queryAll() throws SQLException { ArrayList<Post> list = new ArrayList<Post>(); String sql = "SELECT * FROM posts"; Connection conn = getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); rs.beforeFirst(); while (rs.next()) { Post temp = new Post(); temp.setId(rs.getInt("id")); temp.setTitle(rs.getString("title")); temp.setContent(rs.getString("content")); temp.setDate(rs.getTimestamp("dt_create")); temp.setVisible(rs.getBoolean("visible")); list.add(temp); } stmt.close(); conn.close(); return list; } public static void main(String[] args) { try { Properties props = new Properties(); props.load(ClassLoader.getSystemResourceAsStream("db.mysql.props")); StatementExample example = new StatementExample(); example.setDBProperties(props); ArrayList<Post> posts = example.queryAll(); System.out.println(posts); Post toInsert = new Post(); toInsert.setTitle("new Post"); toInsert.setContent("This is a new post!"); example.insertPost(toInsert); posts = example.queryAll(); System.out.println(posts); example.deleteAll(); posts = example.queryAll(); System.out.println(posts); } catch (SQLException e) { DBUtils.printSQLException(e); } catch (Exception e) { e.printStackTrace(); } } } 《本文章来自于www.yuucn.com 》

PreparedStatement 例子. 《本文来自自学网 www.yuucn.com 》

public class PreparedStatExample { private Properties dbProps = new Properties(); PreparedStatExample() {} public void setDBProperties(Properties dbProps) { this.dbProps = dbProps; } public Connection getConnection() throws SQLException { String url = dbProps.getProperty("url"); String user = dbProps.getProperty("user"); Connection conn = null; if (user.length() == 0) { conn = DriverManager.getConnection(url); } else { conn = DriverManager.getConnection(url, dbProps); } String dbName = dbProps.getProperty("dbName"); conn.setCatalog(dbName); return conn; } public void deletePost(int id) throws SQLException { String sql = "DELETE FROM posts WHERE id = ?"; Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql); stmt.setInt(1, id); stmt.executeUpdate(); DBUtils.printWarnings(stmt.getWarnings()); stmt.close(); conn.close(); } public void insertPost(Post post) throws SQLException { String sql = "INSERT INTO posts VALUES(NULL, ?, ?, DEFAULT, ?)"; Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql); stmt.setString(1, post.getTitle()); stmt.setString(2, post.getContent()); stmt.setBoolean(3, post.isVisible()); stmt.executeUpdate(); DBUtils.printWarnings(stmt.getWarnings()); stmt.close(); conn.close(); } public ArrayList<Post> queryByTitle(String title) throws SQLException { ArrayList<Post> list = new ArrayList<Post>(); String sql = "SELECT * FROM posts WHERE title like ?"; Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql); stmt.setString(1, title); ResultSet rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { Post temp = new Post(); temp.setId(rs.getInt("id")); temp.setTitle(rs.getString("title")); temp.setContent(rs.getString("content")); temp.setDate(rs.getTimestamp("dt_create")); temp.setVisible(rs.getBoolean("visible")); list.add(temp); } stmt.close(); conn.close(); return list; } public static void main(String[] args) { try { Properties props = new Properties(); props.load(ClassLoader.getSystemResourceAsStream("db.mysql.props")); PreparedStatExample example = new PreparedStatExample(); example.setDBProperties(props); // 此时数据库中有一条 title 为 111 的数据 ArrayList<Post> posts = example.queryByTitle("111"); System.out.println(posts); //[Post{id=34, title='111', content='111', date=2015-01-25 12:58:32.0, visible=true}] Post toInsert = new Post(); toInsert.setTitle("111"); toInsert.setContent("111111"); example.insertPost(toInsert); posts = example.queryByTitle("111"); System.out.println(posts); // [Post{id=39, title='111', content='111', date=2015-01-25 13:00:49.0, visible=true}, Post{id=41, title='111', content='111111', date=2015-01-25 13:00:59.0, visible=false}] example.deletePost(posts.get(0).getId()); posts = example.queryByTitle("111"); System.out.println(posts); // [Post{id=41, title='111', content='111111', date=2015-01-25 13:00:59.0, visible=false}] } catch (SQLException e) { DBUtils.printSQLException(e); } catch (Exception e) { e.printStackTrace(); } } }
《本文来自自学网 www.yuucn.com 》

(责任编辑:admin)
网友评论

发表评论

评论列表(条)

    相关阅读
    最近更新