pdf-1 - Uni Marburg

1.
Setup
1. Mit dieser Anleitung (http://www.unimarburg.de/fb12/sys/services/svc_more_html#svc_sql) eine Datenbank einrichten.
2. Installieren des MySQL Workbench (Version 5.2.43)
3. Unter Database > Manage Connection folgende Werte eintragen
login.mathematik.uni-marburg.de
Dein Username ab FB12 (Unix)
mysql.mathematik.uni-marburg.de
Dein Username ab FB12 (Unix)
4. Zweimal das UNIX-Passwort eingeben
5. Eigen Datenbank (Username) suchen und durch Doppelklick auswählen
6. Eine Tabelle anlegen:
CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address char(50),
City char(50), Country char(25), Birth_Date date)
7. Einen Datensatz anlagen:
INSERT INTO customer (First_Name,Last_Name,Address,City,Country,Birth_Date) VALUES
('John','Doe','Hans-Meerwein Strasse','Marburg','Germany','1970-01-10');
8. Resultat kontrollieren
SELECT * FROM customer;
2. Android – Client
1. In der AndroidManifest.xml den Folgenden Eintrag hinzufügen:
<uses-permission android:name="android.permission.INTERNET"></usespermission>
2. Im Java Build-Path müssen die Bibliotheken
a. Jsch-0.1.49.jar (SSH) http://stackoverflow.com/questions/1968293/connect-toremote-mysql-database-through-ssh-using-java
b. Mysql-connector-java-3.0.17-ga-bin.jar (JBCD)
eingebunden und exportiert werden.
3. Folgendes Layout anlegen
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity" >
<EditText
android:id="@+id/editText1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_above="@+id/button1"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_alignParentTop="true"
android:ems="10"
android:inputType="textMultiLine" >
<requestFocus />
</EditText>
<Button
android:id="@+id/button1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentBottom="true"
android:layout_alignParentRight="true"
android:text="Verbindungstest ..." />
</RelativeLayout>
4. Folgende Activity Klasse
package com.example.datenbankzugriff;
import
import
import
import
import
import
import
import
com.example.datenbankzugriff.R;
android.os.Bundle;
android.app.Activity;
android.view.Menu;
android.view.View;
android.widget.Button;
android.widget.EditText;
android.widget.TextView;
public class MainActivity extends Activity {
TextView editText = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
final Button button = (Button) findViewById(R.id.button1);
editText = (EditText) findViewById(R.id.editText1);
button.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
connect();
}
});
}
public void connect(){
ConnectionTask connectionTask = new ConnectionTask();
connectionTask.execute();
try {
editText.setText(connectionTask.get());
} catch (Exception e) {
editText.setText((CharSequence) e);
}
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is
present.
getMenuInflater().inflate(R.menu.activity_main, menu);
return true;
}
}
5. Die Connection Task – Klasse
package com.example.datenbankzugriff;
import
import
import
import
import
java.sql.Connection;
java.sql.DriverManager;
java.sql.ResultSet;
java.sql.Statement;
java.util.Hashtable;
import android.os.AsyncTask;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
class ConnectionTask extends AsyncTask<Void, Integer, String> {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//external database
static final String
static final String
static final String
connection
DB_URL = "jdbc:mysql://localhost:3399/svaupel";
USER = "svaupel";
PASS = "";
//ssh connection
static final String ssh_user = "svaupel";
static final String ssh_server = "137.248.122.61"; //
login.mathematik.uni-marburg.de
static final String ssh_pass = PASS;
static final int ssh_port = 22;
static final String rhost = "137.248.123.188";
mysql.mathematik.uni-marburg.de
static final int rport = 3306;
static final int lport = 3399;
protected String doInBackground(Void... params) {
//
Session session = null;
Connection conn = null;
Statement stmt = null;
String log = "";
try
{
// SSH Connection
log = log + "\n"+"Connecting to ssh-login...";
final JSch jsch = new JSch();
session = jsch.getSession(ssh_user, ssh_server, ssh_port);
log = log + "\n"+"Got session...";
final Hashtable<String, String> config = new
Hashtable<String, String>();
config.put("StrictHostKeyChecking", "no");
session.setConfig(config);
log = log + "\n"+"Session setted ...";
log = log + "\n"+"Session set ssh-password...";
session.setPassword(ssh_pass);
session.connect();
log = log + "\n"+"Session connected...";
int assigned_port = session.setPortForwardingL(lport, rhost,
rport);
log = log + "\n"+"Port forwarding was successful...";
// MySQL Connection
Class.forName(JDBC_DRIVER);
log = log + "\n"+"Connecting to a selected database...";
conn = DriverManager.getConnection(DB_URL, USER, PASS);
log = log + "\n"+"Connected database successfully...";
log = log + "\n"+"Creating statement...";
stmt = conn.createStatement();
log = log + "\n"+"Created statement successfully...\n";
log = log + "\n"+"---------------------------------\n";
String sql;
sql = "SELECT * FROM customer";
//sql = "INSERT INTO customer
(First_Name,Last_Name,Address,City,Country,Birth_Date) VALUES
('John','Doe','Hans-Meerwein Strasse','Marburg','Germany','1970-01-10');";
stmt = conn.createStatement();
ResultSet resultset = stmt.executeQuery(sql);
while (resultset.next()){
log = log + resultset.getString("First_Name") +", "+
resultset.getString("Last_Name") +"\n";
}
}
catch (Exception e)
{
log = log +"/n"+e;
}
return log;
}
}
6. Ergebnis