当前位置： 移动技术网 > IT编程>开发语言>Java > java jdbc连接和使用详细介绍

java jdbc连接和使用详细介绍

2019年07月22日  | 移动技术网IT编程  | 我要评论

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网