Admin: 1 версия

2010-06-01T05:06:07Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== Adding a New Column Name in Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
int n = st.executeUpdate("ALTER TABLE mytable ADD col int");
System.out.println("Query OK, " + n + " rows affected");
}
}</source>



== Arrange a Column of Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("SELECT col_name FROM mytable ORDER BY col_name ASC");
while (res.next()) {
int col = res.getInt(1);
System.out.println(col);
}
}
}</source>



== Change Column Name of a Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
int n = st.executeUpdate("ALTER TABLE myTable CHANGE old_col new_col int");
System.out.println("Query OK, " + n + " rows affected");
}
}</source>



== Copy data from one table to another in a database ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbc4";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
int rows = st.executeUpdate("INSERT INTO Copyemployee SELECT * FROM employee");
if (rows == 0) {
System.out.println("Don"t add any row!");
} else {
System.out.println(rows + " row(s)affected.");
conn.close();
}
}
}</source>



== Copy One Database Table to Another ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbcMysql";
String userName = "root";
String password = "root";
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
int rows = st.executeUpdate("INSERT INTO myTable SELECT * FROM jdbc4.Copyemployee");
if (rows == 0) {
System.out.println("Don"t add any row!");
} else {
System.out.println(rows + " row(s)affected.");
conn.close();
}
}
}</source>



== Create a table in database ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
String username = "username";
String password = "welcome";
String sql = "CREATE TABLE books (id NUMBER(11), title VARCHAR2(64))";
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
statement.execute(sql);
connection.close();
}
}</source>



== Delete a Column from a Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
st.executeUpdate("ALTER TABLE mytable DROP col");
System.out.println("Column is deleted successfully!");
}
}</source>



== Delete record from table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
public static void main(String[] args) throws Exception {
String url = "jdbc:mysql://localhost/sampledb";
String username = "root";
String password = "";
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
String sql = "DELETE FROM users WHERE user_id = ?";
int userId = 2;
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, userId);
int rows = statement.executeUpdate();
System.out.println(rows + " record(s) deleted.");
connection.close();
}
}</source>



== Description of Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("DESCRIBE myTable");
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount();
for (int i = 1; i <= col; i++) {
String col_name = md.getColumnName(i);
System.out.println(col_name);
}
DatabaseMetaData dbm = con.getMetaData();
ResultSet rs1 = dbm.getColumns(null, "%", "myTable", "%");
while (rs1.next()) {
String col_name = rs1.getString("COLUMN_NAME");
String data_type = rs1.getString("TYPE_NAME");
int data_size = rs1.getInt("COLUMN_SIZE");
int nullable = rs1.getInt("NULLABLE");
System.out.println(col_name + " " + data_type + "(" + data_size + ")");
if (nullable == 1) {
System.out.println("YES");
} else {
System.out.println("NO");
}
}
}
}</source>



== Drop table from database ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:demo";
String username = "java";
String password = "";
String sql = "DROP TABLE books";
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
statement.execute(sql);
connection.close();
}
}</source>



== Make Unique Column in Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
int n = st.executeUpdate("ALTER TABLE mytable ADD UNIQUE( colName)");
System.out.println("Query OK, " + n + " rows affected.");
}
}</source>



== Remove Unique Column in Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
int n = st.executeUpdate("ALTER TABLE mytable DROP INDEX col");
System.out.println("Query OK, " + n + " rows affected.");
}
}</source>



== Sum of Column in a Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
int sum = 0;
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
"root", "root");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("SELECT SUM(col) FROM mytable");
while (res.next()) {
int c = res.getInt(1);
sum = sum + c;
}
System.out.println("Sum of column = " + sum);
}
}</source>

← Предыдущая	Версия 05:06, 1 июня 2010
(нет различий)

Java Tutorial/Database/Table - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010