Java jdbc connection and use
jdbc
Import drivers
//jar is a packaged class file set, which can be referenced to other projects
//Import external jars in Build Path
Connect to JDBC
1. Load the driver
Class.from("com.mysql.jdbc.Driver");Create a connection
//The guide package uses java.sql.*; String jdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";//student is the table name Connection conn = DriverManager.getConnection(jdbc);
2. Note that after the database is opened, remember to close it.
conn.close();
1. Execute SQL statements (create tables, insert, delete, update)
Using Statemant
Statemant st = conn.createStatemant(); int row = st.executeUpdate(sql statement);//Cannot do query operations.
Using PrepareStatement
Can it be used? Placeholders to replace the parameters you need to pass
String sql = "insert into " + TABLENAME+ "(name,subject,score) values(?,?,?)";PrepareStatement pt = conn.prepareStatement(sql); //Set the value for each placeholder, and the subscript starts from 1 pt.setString(1, score.getName()); pt.setString(2.score.getSubject()); pt.setDouble(3, score.getScore()); //Use the method without parameters pt.executeUpdate();
1. Query operation
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()) { // Here you can get all results through 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; }Here is a small program
//Create a database connection class public class DAO { // Ask the database link address static String jdbc = "jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8"; // Open the link public static Connection connection() { // Steps to using JDBC// 1. Load the JDBC driver try { // Full name of the class + class name Class.forName("com.mysql.jdbc.Driver"); // 2. Connect to the database Connection conn = DriverManager.getConnection(jdbc); return conn; } catch (Exception e) { System.out.println("Driver load failed"); return null; } }}//Score class 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; }}//Implementation class 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("linked"); // createTable(conn); // Insert a record// Score score = new Score("Li Si", "Android", 98); // System.out.println(addScore2(conn, score)); // deleteScore(conn, score); // updateScore(conn, score); List<Score> list = queryScoreByName(conn, "Wang Wu"); //queryAllScore(conn); for (Score score : list) { System.out.println(score); } conn.close(); } else { System.out.println("Link failed"); } } catch (SQLException e) { e.printStackTrace(); } } // Create a table public static boolean createTable(Connection conn) { // Start executing the sql statement String sql = "create table " + TABLENAME + "(id integer primary key auto_increment,name varchar(3) not null,subject varchar(20) not null,score double)"; // To execute a statement, an executed class needs to be executed 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; } // Add a record 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; } // Add a record 2 public static boolean addScore2(Connection conn, Score score) throws SQLException { // Placeholder? to replace the parameter that needs to be set String sql = "insert into " + TABLENAME + "(name,subject,score) values(?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); // Must be given? The value represented by ps.setString(1, score.getName()); ps.setString(2, score.getSubject()); ps.setDouble(3, score.getScore()); // Call the method without parameters 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=?"; // Create PrepareStatement PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, score.getName()); ps.setString(2, score.getSubject()); // ps.setDouble(3, score.getScore()); // Execute 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 { // Modify score for his subjects 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; // Start query Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); List<Score> list = new ArrayList<Score>(); while (rs.next()) { // Here you can get all results through 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)); } // End 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; }