java jdbc连接串_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 queryScore(Connection pconn, Score pScore)

throws SQLException {

ArrayList 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 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 queryAllScore(Connection conn)

throws SQLException {

String sql = "select * from " + TABLENAME;

// 开始查询

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(sql);

List list = new ArrayList();

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

}