java jdbc連接和使用詳細介紹

java jdbc連接和使用

jdbc

導入驅動

//jar是已經打包好的class文件集，可以引用到其他工程中
//Build Path中add external jars導入

連接JDBC

1. 加載驅動

 Class.from("com.mysql.jdbc.Driver");

創建連接

//導包使用java.sql.*；String jdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";//student是表名Connection conn = DriverManager.getConnection(jdbc);

2. 注意數據庫打開之後一定要記得關。

 conn.close();

1. 執行SQL語句（創建表，插入，刪除，更新）

使用Statemant

 Statemant st = conn.createStatemant(); int row = st.executeUpdate(sql語句);//不能做查詢操作。

使用PrepareStatement

可以使用？佔位符來代替你需要傳遞的參數

String sql = "insert into " + TABLENAME+ "(name,subject,score) values(?,?,?)";PrepareStatement pt = conn.prepareStatement(sql); //給每一位佔位符設置值,下標從1開始pt.setString(1,score.getName()); pt.setString(2.score.getSubject());pt.setDouble(3,score.getScore()); //使用無參的方法pt.executeUpdate();

1.查詢操作

static List<Score> queryScore(Connection pconn, Score pScore) throws SQLException { ArrayList<Score> mlist = new ArrayList<>(); String sql = "select * from " + TABLENAME + " where name = ?"; PreparedStatement ps = pconn.prepareStatement(sql); ps.setString(1, pScore.getName()); ResultSet rs = ps.executeQuery(); while (rs.next()) { // 這裡可以通過rs獲取所有結果String subject = rs.getString("subject"); int id = rs.getInt("id"); double score = rs.getDouble("score"); mlist.add(new Score(id, pScore.getName(), subject, score)); } return mlist; }

下面是一個小程序

//建立數據庫連接類public class DAO { // 放問數據庫的鏈接地址static String jdbc = "jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8"; // 打開鏈接public static Connection connection() { // 使用JDBC的步驟// 1. 加載JDBC驅動try { // 類的全名包名+類名Class.forName("com.mysql.jdbc.Driver"); // 2. 連接數據庫Connection conn = DriverManager.getConnection(jdbc); return conn; } catch (Exception e) { System.out.println("驅動加載失敗"); return null; } }}//分數類public class Score { String name; String id; String subject; double score; public Score(String name, String subject, double score) { super(); this.name = name; this.subject = subject; this.score = score; } @Override public String toString() { return "Score [name=" + name + ", id=" + id + ", subject=" + subject + ", score=" + score + "]"; } public Score(String name, String id, String subject, double score) { super(); this.name = name; this.id = id; this.subject = subject; this.score = score; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getSubject() { return subject; } public void setSubject(String subject) { this.subject = subject; } public double getScore() { return score; } public void setScore(double score) { this.score = score; }}//實現類public class Test { public static String TABLENAME = "score"; public static void main(String[] args) { try { Connection conn = DAO.connection(); if (conn != null) { System.out.println("鏈接上了"); // createTable(conn); // 插入一條記錄// Score score = new Score("李四", "Android", 98); // System.out.println(addScore2(conn, score)); // deleteScore(conn, score); // updateScore(conn, score); List<Score> list = queryScoreByName(conn, "王五"); //queryAllScore(conn); for (Score score : list) { System.out.println(score); } conn.close(); } else { System.out.println("鏈接失敗"); } } catch (SQLException e) { e.printStackTrace(); } } // 創建一張表public static boolean createTable(Connection conn) { // 開始執行sql語句String sql = "create table " + TABLENAME + "(id integer primary key auto_increment,name varchar(3) not null,subject varchar(20) not null,score double)"; // 要執行一條語句，需要一個執行的類Statement try { Statement st = conn.createStatement(); int result = st.executeUpdate(sql); System.out.println(result); if (result != -1) return true; } catch (SQLException e) { e.printStackTrace(); } return false; } // 添加一條記錄public static boolean addScore(Connection conn, Score score) throws SQLException { String sql = "insert into " + TABLENAME + "(name,subject,score) values('" + score.getName() + "','" + score.getSubject() + "'," + score.getScore() + ")"; System.out.println(sql); Statement st = conn.createStatement(); int row = st.executeUpdate(sql); if (row > 0) return true; return false; } // 添加一條記錄2 public static boolean addScore2(Connection conn, Score score) throws SQLException { // 佔位符?來代替需要設置的參數String sql = "insert into " + TABLENAME + "(name,subject,score) values(?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); // 必須給定？所代表的值ps.setString(1, score.getName()); ps.setString(2, score.getSubject()); ps.setDouble(3, score.getScore()); // 調用無參的方法int row = ps.executeUpdate(); if (row > 0) return true; return false; } public static boolean deleteScore(Connection conn, Score score) throws SQLException { String sql = "delete from " + TABLENAME + " where name=? and subject=?"; // 創建PrepareStatement PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, score.getName()); ps.setString(2, score.getSubject()); // ps.setDouble(3, score.getScore()); // 執行int row = ps.executeUpdate(); System.out.println(row); if (row > 0) return true; return false; } public static boolean updateScore(Connection conn, Score score) throws SQLException { // 修改score人他的科目的成績String sql = "update " + TABLENAME + " set score=? where name=? and subject=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDouble(1, score.getScore()); ps.setString(2, score.getName()); ps.setString(3, score.getSubject()); int row = ps.executeUpdate(); System.out.println(row); if (row > 0) return true; return false; } public static List<Score> queryAllScore(Connection conn) throws SQLException { String sql = "select * from " + TABLENAME; // 開始查詢Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); List<Score> list = new ArrayList<Score>(); while (rs.next()) { // 這裡可以通過rs獲取所有結果String id = rs.getString("id"); String name = rs.getString("name"); String subject = rs.getString("subject"); double score = rs.getDouble("score"); list.add(new Score(name, id, subject, score)); } // 結束return list; } public static List<Score> queryScoreByName(Connection conn, String name) throws SQLException { String sql = "select * from " + TABLENAME + " where name=?"; PreparedStatement pt = conn.prepareStatement(sql); pt.setString(1, name); ResultSet rs = pt.executeQuery(); List<Score> list = new ArrayList<>(); while (rs.next()) { String subject = rs.getString("subject"); String id = rs.getString("id"); double score = rs.getDouble("score"); list.add(new Score(name, id, subject, score)); } return list; }