java jdbc connection and use details
- 2020-05-26 08:34:25
- OfStack
java jdbc connect and use
jdbc
The import drivers
//jar is a packaged set of class files that can be referenced to other projects
//Build Path add external jars import
Connection JDBC
1. Load the driver
Class.from("com.mysql.jdbc.Driver");
Create a connection
// Guide package use java.sql.* ;
String jdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";//student Is the name of the table
Connection conn = DriverManager.getConnection(jdbc);
2. Remember to close 1 after the database is opened.
conn.close();
1. Execute SQL statement (create table, insert, delete, update)
Using Statemant
Statemant st = conn.createStatemant();
int row = st.executeUpdate(sql statements );// Cannot do query operation.
Using PrepareStatement
Can you use it? Placeholders to replace the arguments you need to pass
String sql = "insert into " + TABLENAME
+ "(name,subject,score) values(?,?,?)";
PrepareStatement pt = conn.prepareStatement(sql);
// For each 1 The bit holder sets the value , The subscript from 1 start
pt.setString(1,score.getName());
pt.setString(2.score.getSubject());
pt.setDouble(3,score.getScore());
// Use the method without arguments
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 go through rs Get all the results
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 link address of the database
static String jdbc = "jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";
// Open the link
public static Connection connection() {
// use JDBC The steps of
// 1. loading JDBC drive
try {
// The full name of a class The package name + The name of the class
Class.forName("com.mysql.jdbc.Driver");
// 2. Connect to database
Connection conn = DriverManager.getConnection(jdbc);
return conn;
} catch (Exception e) {
System.out.println(" Driver load failed ");
return null;
}
}
}
// Grade 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;
}
}
// The 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(" Link on the ");
// createTable(conn);
// insert 1 records
// Score score = new Score(" li 4 ", "Android", 98);
// System.out.println(addScore2(conn, score));
// deleteScore(conn, score);
// updateScore(conn, score);
List<Score> list = queryScoreByName(conn, " The king 5"); //queryAllScore(conn);
for (Score score : list) {
System.out.println(score);
}
conn.close();
} else {
System.out.println(" A link failure ");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// create 1 table
public static boolean createTable(Connection conn) {
// Start to perform sql statements
String sql = "create table "
+ TABLENAME
+ "(id integer primary key auto_increment,name varchar(3) not null,subject varchar(20) not null,score double)";
// To perform 1 Statement, yes 1 Class for execution 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 1 records
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 1 records 2
public static boolean addScore2(Connection conn, Score score)
throws SQLException {
// A placeholder ? In place of the parameters that need to be set
String sql = "insert into " + TABLENAME
+ "(name,subject,score) values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
// Must be given? The value represented
ps.setString(1, score.getName());
ps.setString(2, score.getSubject());
ps.setDouble(3, score.getScore());
// Calls a method with no arguments
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());
// perform
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 the score He got good grades in 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;
// Began to query
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
List<Score> list = new ArrayList<Score>();
while (rs.next()) {
// Here you can go through rs Get all the results
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));
}
// The end of the
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;
}