MySql performs the JDBC join (Add/delete/modify/query) operation, and mysqljdbc
Video address: http://www.tudou.com/programs/view/4GIENz1qdp0/
Create BaseDao
Package cn. wingfly. dao; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; public class BaseDao {Connection con = null; Statement st = null; ResultSet rs = null;/*** get join ** @ return */public Connection getConnection () {try {// load the driver. This sentence can also be written as: Class. forName ("com. mysql. jdbc. driver "); Class. forName ("com. mysql. jdbc. drive R "). newInstance (); // connect to MySQL con = DriverManager. getConnection (" jdbc: mysql: // localhost: 3306/money_note? CharacterEncoding = UTF-8 "," root "," root ");} catch (Exception e) {e. printStackTrace ();} return con;}/*** close the data source */public void CloseConnection (Connection con, Statement s, ResultSet rs) {try {if (rs! = Null) {rs. close () ;}if (s! = Null) {s. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e) {e. printStackTrace ();}}}
Test class
Package cn. wingfly. dao; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; public class Test extends BaseDao {Connection con = null; Statement st = null; ResultSet rs = null;/*** query data */public void find () {con = getConnection (); // obtain the join try {st = con. createStatement (); rs = st.exe cuteQuery ("select * from app_user"); while (rs. next () {System. out. println ("No.:" + rs. getInt ("uuid") + ", name:" + rs. getString ("userName") + ", Gender:" + rs. getString ("sex") + ", birthday:" + rs. getString ("birthday") + ", address:" + rs. getString ("address") ;}} catch (SQLException e) {e. printStackTrace ();} finally {CloseConnection (con, st, rs); // close the connection}/*** add data */public void add () {con = getConnection (); try {st = con. createStatement (); int result = st.exe cuteUpdate ("insert into app_user (userName, passWord, sex, birthday, address) values ('zhao liying', 'wanying', 'female ', '1970-02-03 ', 'beijing') "); if (result> 0) {System. out. println ("inserted successfully");} else {System. out. println ("insertion failed") ;}} catch (SQLException e) {System. out. println ("insertion failed"); e. printStackTrace () ;}finally {CloseConnection (con, st, rs) ;}/ *** update data */public void update () {con = getConnection (); try {st = con. createStatement (); int result = st.exe cuteUpdate ("update app_user set address = 'henan 'where uuid = '3'"); if (result> 0) {System. out. println ("updated successfully");} else {System. out. println ("update failed") ;}} catch (SQLException e) {e. printStackTrace () ;}finally {CloseConnection (con, st, rs) ;}}/*** delete data */public void delete () {con = getConnection (); try {st = con. createStatement (); int result = st.exe cuteUpdate ("delete from app_user where uuid = '3'"); if (result> 0) {System. out. println ("deleted successfully");} else {System. out. println ("failed to delete") ;}} catch (SQLException e) {e. printStackTrace () ;}finally {CloseConnection (con, st, rs) ;}} public static void main (String [] args) {Test test = new Test (); // test. add (); // test. update (); test. delete (); test. find ();}}
In java, how does jdbc add, delete, modify, and query mysql? What are the disadvantages of connecting to mysql through jdbc? Are you sure you are not charged for mysql?
/**
* Query the total number of records in the database
*/
Public int findAllEmailCount (){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String SQL = "select count (*) from email ";
Int count = 0;
Try {
Conn = DBHelp. getConnection ();
Pstmt = conn. prepareStatement (SQL );
Rs = pstmt.exe cuteQuery ();
If (rs. next ()){
Count = rs. getInt (1 );
}
} Catch (Exception e ){
Throw new RuntimeException ("database query error ");
} Finally {
DBHelp. closeAll (null, pstmt, conn );
}
Return count;
}
Write a java program to access the database through jdbc for database insertion, deletion, modification and query operations
1. Add
String s1 = "insert into tableNames (id, name, password) values (myseq. nextval ,?,?); "
Class. forName (driver );
Connection conn = DriverManager. getConnection (url, dbUser, dbPwd );
PreparedStatement prepStmt = conn. prepareStatement (s1 );
PrepStmt. setString (1, name );
PrepStmt. setString (2, password );
ResultSet rs1_stmt.exe cuteUpdate ();
2. Delete
String s2 = "delete from tbNames where name =? ";
Class. forName (driver );
Connection conn = DriverManager. getConnection (url, dbUser, dbPwd );
PreparedStatement prepStmt = conn. prepareStatement (s2 );
PrepStmt. setString (1, name );
ResultSet rs1_stmt.exe cuteUpdate ();
3. Modify
String s3 = "update tbNames set name =? Where id = ?";
Class. forName (driver );
Connection conn = DriverManager. getConnection (url, dbUser, dbPwd );
PreparedStatement prepStmt = conn. prepareStatement (s3 );
PrepStmt. setString (1, name );
PrepStmt. setString (2, id );
ResultSet rs1_stmt.exe cuteUpdate ();
4. Query
String s4 = "select id, name, password from tbNames ";
Class. forName (driver );
Connection conn = DriverManager. getConnection (url, dbUser, dbPwd );
Statement stmt = conn. createStatement ();
ResultSet rs = stmt.exe cuteQuery (s4 );
While (rs. next ){
Int id = rs. getInt (1 );
String name = rs. getString (2 );
String pwd = rs. getString (3 );
System. out. println (id + name + pwd );}
The connection must be closed in the above four steps ;!!!
Rs. close ();
Stmt. close ();
Conn. close ();... the remaining full text>