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; }